I like this method (one correction I believe it not just t but min of t or c that makes up time) .. .you could probably select the obs of the max time into a macro variable and use that in the update where and avoid create the temp table all together. EJ ... View more

Yeah in this case this two methods are the same. EJ ... View more

Is this what you need? Proc sort data=test out=test2; by descending time; run; data test3;      set test2;      by descending time;      if first.time then censored = 0; run; ... View more

and Correct me if Im wrong but I dont think either of your methods account for PIDs that are not in the source dataset. So if a full set is desired some would be missing. Also believe Ksharp code just flags if a visit happens in a month and not the number of visits in that month, (again forgive if I missed something). EJ ... View more

The sql is doing the counts and creating a full set of ids months to merge the counts into. If I remember correctly a data step merge will not give you a cartesian product (all the ids months combinations) in a long format instead of the wide you desire. You can repalce the sql count with proc freq and an output statement (with some additional cleanup) You can replace the two distincts with proc sort  nodupkey Im sure others can provide better data step suggestions. EJ ... View more

This is quick and dirty so some optimization could probably be done but hopefully this gets you going: A couple of notes: 1) The card statements are just getting test data in. You need a table with all your ids present and all the months you want to use present so the sql can make the cartesian product between the ids and months. 2) Some of the sql steps can be combined, but I left them separate so it was easier to see what was happening. infile cards dlm=','; input pid month; cards; 1, 3 2, 5 4, 6 4, 1 5, 12 1, 3 8, 5 4, 6 4, 2 5, 12 ; run; data all; infile cards ; input pid; cards; 1 2 4 3 4 5 5 6 7 8 8 9 2 10 ; run; data mnthin; infile cards; input month; cards; 1 2 3 4 4 4 5 5 6 7 8 8 9 10 11 12 ; run; proc sql; create table cnts as select pid, month, count(*) as cnt from in group by pid, month ; create table ids as select distinct pid from all ; create table mnth as select distinct month from mnthin ; create table cartesian as select a.pid, b.month from ids as a , mnth as b ; create table out1 as select a.pid, a.month, coalesce(b.cnt,0) as cnt from cartesian as a left join cnts as b on a.pid =b.pid and a.month = b.month order by pid, month ; quit; Hope this helps! EJ data out2; set out1; by pid month; if first.pid then do; vis_jan = 0; vis_feb = 0; vis_mar = 0; vis_apr = 0; vis_may = 0; vis_jun = 0; vis_jul = 0; vis_aug = 0; vis_sep = 0; vis_oct = 0; vis_nov = 0; vis_dec = 0; end; select (month); when (1) vis_jan = cnt; when (2) vis_feb = cnt; when (3) vis_mar = cnt; when (4) vis_apr = cnt; when (5) vis_may = cnt; when (6) vis_jun = cnt; when (7) vis_jul = cnt; when (8) vis_aug = cnt; when (9) vis_sep = cnt; when (10) vis_oct = cnt; when (11) vis_nov = cnt; when (12) vis_dec = cnt; end; retain vis: ; drop cnt month; if last.pid then output; run; ... View more