怎样计算列变量两组对象间时间差

有一批数据，格式如下

1. name  date       group
   
2. 白云        2016/12/11  1
   
3. 白云        2017/10/25  1
   
4. 白云        2017/6/12          2
   
5. 白云        2017/12/25        2
   
6. 白云        2017/11/22        2
   
7. 白云        2016/9/20          2
   
8. 白云        2016/8/10          2
   
9. 鲍宝芝 2017/10/15 1
   
10. 鲍宝芝        2017/7/2        1
    
11. 鲍宝芝        2017/8/11        2
    
12. 毕蕾        2016/8/26   1
    
13. 毕蕾        2016/12/23  2
    
14. 毕蕾        2016/10/21  2

复制代码

现在想计算：name相同的记录，group=1的（可能有2个以上）与group=2的date之周数差，其差值分别生成week1、week2...(按照group总数减1），怎样编程？谢谢。

Rock2000 发表于 2018-4-18 18:17
其实我有很笨方法如下：

1. data test;
   
2. input name $ date:yymmdd10.  mark;
   
3. format date yymmdd10.;
   
4. cards;
   
5. 白云      2016/12/11  1
   
6. 白云      2017/10/25  1
   
7. 白云      2017/6/12   2
   
8. 白云      2017/12/25  2
   
9. 白云      2017/11/22  2
   
10. 白云      2016/9/20   2
    
11. 白云      2016/8/10   2
    
12. 鲍宝芝    2017/10/15  1
    
13. 鲍宝芝    2017/7/2    1
    
14. 鲍宝芝    2017/8/11   2
    
15. 毕蕾      2016/8/26   1
    
16. 毕蕾      2016/12/23  2
    
17. 毕蕾      2016/10/21  2
    
18. 张三        2016/8/26   1
    
19. 张三        2016/9/23  1
    
20. 张三        2016/10/21  1
    
21. 张三        2016/12/26  1
    
22. 张三        2017/12/23  2
    
23. 张三        2017/10/21  2
    
24. ;
    
25. run;
    
26. 
    
27. data tmp1;
    
28.   set test;
    
29.   by name mark;
    
30. 
    
31.   retain grp 1;
    
32.   grp = ifn(first.mark,1,grp + 1);
    
33. 
    
34.   sid = _n_;
    
35. run;
    
36. 
    
37. proc sql;
    
38.   create table tmp2 as
    
39.   select a.name, a.date as date_g1, b.date as date_g2, a.sid as sid1, b.sid as sid2, intck('day', a.date, b.date) as weeks
    
40.   from tmp1 a full join tmp1 b
    
41.   on a.name = b.name
    
42.   where a.mark = 1 and b.mark = 2
    
43.   order by name, sid2, sid1, date_g1;
    
44. quit;
    
45. 
    
46. proc transpose data = tmp2 out = tmp_sub prefix= week;
    
47.   by name sid2;
    
48.   var weeks;
    
49. quit;
    
50. 
    
51. proc sql;
    
52.   create table final(drop = _:) as
    
53.   select a.name, a.date, a.mark, a.sid, a.grp, b.*
    
54.   from tmp1 a left join tmp_sub(rename=(name = _name sid2 = _sid)) b
    
55.   on a.name = b._name and a.sid = b._sid
    
56.   ;
    
57. quit;

复制代码

和你的结果compare了一下，totally match

哪位帮忙想想？如果不能发帖，可以私聊我，谢谢。

编辑掉，来跟大神学习下

1. data have(drop = mass);
   
2.   input mass $50.;
   
3.   name = scan(mass,1,' ');
   
4.   format date yymmdd10.;
   
5.   date = input(scan(mass,2,' '),yymmdd10.);
   
6.   group = input(scan(mass,3,' '),best.);
   
7. cards;
   
8. 白云        2016/12/11  1
   
9. 白云        2017/10/25  1
   
10. 白云        2017/6/12          2
    
11. 白云        2017/12/25        2
    
12. 白云        2017/11/22        2
    
13. 白云        2016/9/20          2
    
14. 白云        2016/8/10          2
    
15. 鲍宝芝 2017/10/15 1
    
16. 鲍宝芝        2017/7/2        1
    
17. 鲍宝芝        2017/8/11        2
    
18. 毕蕾        2016/8/26   1
    
19. 毕蕾        2016/12/23  2
    
20. 毕蕾        2016/10/21  2
    
21. ;run;
    
22. 
    
23. proc sort data = have;
    
24.   by name group date;
    
25. quit;
    
26. 
    
27. proc sql;
    
28.   create table tmp as
    
29.   select a.name, a.date as date_g1, b.date as date_g2, intck('week', a.date, b.date) as weeks
    
30.   from have a full join have b
    
31.   on a.name = b.name
    
32.   where a.group = 1 and b.group = 2;
    
33. quit;
    
34. 
    
35. proc transpose data = tmp out = tmp2 prefix= week;
    
36.   by name date_g1;
    
37.   var weeks;
    
38. quit;

复制代码

没很看明白最终需求，我写了个大概，或者给个例子？

Tigflanker 发表于 2018-4-18 14:37
没很看明白最终需求，我写了个大概，或者给个例子？

其实我需要的结果是这样
Obsnamedatemarksidgrpweek1week2week3week4
1白云2016-12-11111....
2白云2017-10-25122....
3白云2017-06-12231183-135..
4白云2017-12-2524237961..
5白云2017-11-2225334628..
6白云2016-09-20264-82-400..
7白云2016-08-10275-123-441..
8鲍宝芝2017-10-15181....
9鲍宝芝2017-07-02192....
10鲍宝芝2017-08-112101-6540..
11毕蕾2016-08-261111....
12毕蕾2016-12-232121119...
13毕蕾2016-10-21213256...
14张三2016-08-261141....
15张三2016-09-231152....
16张三2016-10-211163....
17张三2016-12-261174....
18张三2017-12-232181484456428362
19张三2017-10-212192421393365299

比如name=白云mark=1有两条记录，我需要的是name=白云mark=2的其它记录的date分别于mark=1进行计算，我上面结果week1就是mark=2与mark=1第一条分别记录计算的差，week2就是mark=2与mark=1第二条分别记录计算时间差。

其实我有很笨方法如下：

1. data test;
   
2. input name $ date:yymmdd10.  mark;
   
3. format date yymmdd10.;
   
4. cards;
   
5. 白云      2016/12/11  1
   
6. 白云      2017/10/25  1
   
7. 白云      2017/6/12   2
   
8. 白云      2017/12/25  2
   
9. 白云      2017/11/22  2
   
10. 白云      2016/9/20   2
    
11. 白云      2016/8/10   2
    
12. 鲍宝芝    2017/10/15  1
    
13. 鲍宝芝    2017/7/2    1
    
14. 鲍宝芝    2017/8/11   2
    
15. 毕蕾      2016/8/26   1
    
16. 毕蕾      2016/12/23  2
    
17. 毕蕾      2016/10/21  2
    
18. 张三        2016/8/26   1
    
19. 张三        2016/9/23  1
    
20. 张三        2016/10/21  1
    
21. 张三        2016/12/26  1
    
22. 张三        2017/12/23  2
    
23. 张三        2017/10/21  2
    
24. ;
    
25. run;
    
26. 
    
27. data test; set test;
    
28. retain sid 0;
    
29. sid=sid+1;
    
30. run;
    
31. 
    
32. data a; set test; if mark=1; run;
    
33. 
    
34. proc sort data=a; by name; run;
    
35. 
    
36. data dups nodups;
    
37. set a;
    
38. by name;
    
39. if first.name and last.name then output nodups;
    
40. else output dups;
    
41. run;
    
42. 
    
43. data nodups; set nodups; grp=1; run;
    
44. 
    
45. proc sort data=dups; by name; run;
    
46. 
    
47. data dups; set dups; /*对mark=1的记录重新赋于顺序号grp*/
    
48. by name;
    
49. retain grp 0;
    
50. if first.name then grp=1;
    
51. else grp=grp+1;
    
52. run;
    
53. 
    
54. data b; set test; if mark=2;run;
    
55. 
    
56. proc sort data=b; by name; run;
    
57. 
    
58. data b; set b; /*对mark=2的记录重新赋于顺序号grp*/
    
59. by name;
    
60. retain grp 0;
    
61. if first.name then grp=1;
    
62. else grp=grp+1;
    
63. run;
    
64. 
    
65. data c; set dups nodups; run;
    
66. 
    
67. data c2; set c; if grp=2; run;
    
68. data c3; set c; if grp=3; run;
    
69. data c4; set c; if grp=4; run; /*因为mark=1有4个重名的*/
    
70. 
    
71. data d; set b c; run; /*再次合并mark=1 及mark=2*/
    
72. 
    
73. data e1; set d; if (mark=1 and grp=1) or mark=2; run;
    
74. 
    
75. data e2; set d;             /*不能像e1一样取得数据，需要按照C2来取得数据，痛苦*/
    
76. if name="白云" or name="鲍宝芝" or name="张三";
    
77. if mark=1 and grp^=2 then delete;
    
78. run;
    
79. 
    
80. data e3; set d;
    
81. if name="张三";
    
82. if mark=1 and grp^=3 then delete;
    
83. run;
    
84. 
    
85. data e4; set d;  /*如果mark=1有很多就晕了*/
    
86. if name="张三";
    
87. if mark=1 and grp^=4 then delete;
    
88. run;
    
89. 
    
90. proc sort data=e1; by name mark; run; /*恢复原顺序*/
    
91. proc sort data=e2; by name mark; run;
    
92. proc sort data=e3; by name mark; run;
    
93. proc sort data=e4; by name mark; run;
    
94. 
    
95. data e1; set e1;
    
96. by name mark;   
    
97. week=dif1(date);
    
98. week2=dif2(date);
    
99. week3=dif3(date);
    
100. week4=dif4(date);
     
101. week5=dif5(date); /*mark=2总共有5个重名*/
     
102. if grp=2 then week=week2;
     
103. if grp=3 then week=week3;
     
104. if grp=4 then week=week4;
     
105. if grp=5 then week=week5;
     
106. if first.name then week=.;
     
107. drop week2--week5;
     
108. run;
     
109. 
     
110. data e2; set e2;
     
111. by name mark;   
     
112. week=dif1(date);
     
113. week2=dif2(date);
     
114. week3=dif3(date);
     
115. week4=dif4(date);
     
116. week5=dif5(date); /*mark=2总共有5个重名*/
     
117. if grp=2 then week=week2;
     
118. if grp=3 then week=week3;
     
119. if grp=4 then week=week4;
     
120. if grp=5 then week=week5;
     
121. if first.name then week=.;
     
122. drop week2--week5;
     
123. run;
     
124. 
     
125. data e3; set e3;
     
126. by name mark;   
     
127. week=dif1(date);
     
128. week2=dif2(date);
     
129. week3=dif3(date);
     
130. week4=dif4(date);
     
131. week5=dif5(date); /*mark=2总共有5个重名*/
     
132. if grp=2 then week=week2;
     
133. if grp=3 then week=week3;
     
134. if grp=4 then week=week4;
     
135. if grp=5 then week=week5;
     
136. if first.name then week=.;
     
137. drop week2--week5;
     
138. run;
     
139. 
     
140. data e4; set e4;
     
141. by name mark;   
     
142. week=dif1(date);
     
143. week2=dif2(date);
     
144. week3=dif3(date);
     
145. week4=dif4(date);
     
146. week5=dif5(date); /*mark=2总共有5个重名*/
     
147. if grp=2 then week=week2;
     
148. if grp=3 then week=week3;
     
149. if grp=4 then week=week4;
     
150. if grp=5 then week=week5;
     
151. if first.name then week=.;
     
152. drop week2--week5;
     
153. run;
     
154. 
     
155. proc datasets library=work; modify e1; rename week=week1; run;
     
156. proc datasets library=work; modify e2; rename week=week2; run;
     
157. proc datasets library=work; modify e3; rename week=week3; run;
     
158. proc datasets library=work; modify e4; rename week=week4; run;
     
159. 
     
160. proc sort data=e1; by sid; run;
     
161. proc sort data=e2; by sid; run;
     
162. proc sort data=e3; by sid; run;
     
163. proc sort data=e4; by sid; run;
     
164. 
     
165. data f; merge e1 e2 e3 e4;
     
166. by sid;
     
167. run;
     
168. 
     
169. proc print data=f; run;

复制代码

Tigflanker 发表于 2018-4-18 14:37
没很看明白最终需求，我写了个大概，或者给个例子？

想得到的结果如下：

Obsnamedatemarksidgrpweek1week2week3week4
1白云2016-12-11111....
2白云2017-10-25122....
3白云2017-06-12231183-135..
4白云2017-12-2524237961..
5白云2017-11-2225334628..
6白云2016-09-20264-82-400..
7白云2016-08-10275-123-441..
8鲍宝芝2017-10-15181....
9鲍宝芝2017-07-02192....
10鲍宝芝2017-08-112101-6540..
11毕蕾2016-08-261111....
12毕蕾2016-12-232121119...
13毕蕾2016-10-21213256...
14张三2016-08-261141....
15张三2016-09-231152....
16张三2016-10-211163....
17张三2016-12-261174....
18张三2017-12-232181484456428362
19张三2017-10-212192421393365299

比如，name=白云mark=1有2条记录，mark=2有5条记录，现在用mark=2与mark=1的两条记录分别计算时间差，结果中，week1就是mark=2分别与mark=1的第一条记录计算的结果，week2就是mark=2分别与mark=1的第二条记录计算的结果。

Rock2000 发表于 2018-4-18 18:17
其实我有很笨方法如下：

另外，如果您实在着急写句子：

1. proc sort data=e1; by name mark; run; /*恢复原顺序*/
   
2. proc sort data=e2; by name mark; run;
   
3. 
   
4. data e1; set e1;
   
5. by name mark;   
   
6. week=dif1(date);
   
7. week2=dif2(date);
   
8. week3=dif3(date);
   
9. week4=dif4(date);
   
10. week5=dif5(date); /*mark=2总共有5个重名*/
    
11. if grp=2 then week=week2;
    
12. if grp=3 then week=week3;
    
13. if grp=4 then week=week4;
    
14. if grp=5 then week=week5;
    
15. if first.name then week=.;
    
16. drop week2--week5;
    
17. run;
    
18. 
    
19. data e2; set e2;
    
20. by name mark;   
    
21. week=dif1(date);
    
22. week2=dif2(date);
    
23. week3=dif3(date);
    
24. week4=dif4(date);
    
25. week5=dif5(date); /*mark=2总共有5个重名*/
    
26. if grp=2 then week=week2;
    
27. if grp=3 then week=week3;
    
28. if grp=4 then week=week4;
    
29. if grp=5 then week=week5;
    
30. if first.name then week=.;
    
31. drop week2--week5;
    
32. run;

复制代码

等价于：

1. %macro temp:
   
2. 
   
3. *Something else;
   
4. 
   
5. %do i = 1 %to 2;
   
6.   proc sort data=e&i.; by name mark; run;
   
7. %end;
   
8. 
   
9. %do i = 1 %to 2;
   
10.   data e&i.; set e&i.;
    
11.   by name mark;   
    
12.   week=dif1(date);
    
13.   week2=dif2(date);
    
14.   week3=dif3(date);
    
15.   week4=dif4(date);
    
16.   week5=dif5(date); /*mark=2总共有5个重名*/
    
17.   if grp=2 then week=week2;
    
18.   if grp=3 then week=week3;
    
19.   if grp=4 then week=week4;
    
20.   if grp=5 then week=week5;
    
21.   if first.name then week=.;
    
22.   drop week2--week5;
    
23.   run;
    
24. %end;
    
25. 
    
26. *Something else;
    
27. 
    
28. %mend;
    
29. 
    
30. %temp;

复制代码

也可以用一些宏来简化书写

谢谢@Tigflanker