Thank you sir! ... View more

@rajd1 wrote: This is great! Just as a thought if there was a way not to use the threshold value in days... You can use the INTCK function to computee the distance in time. ... View more

Hi @rajd1  Please try the modified and let me know-   data have; input ID alpha $; cards; 1 a 1 b 1 c 2 a 2 c 3 a 3 b 4 a 4 a ; data want; do until(last.id); set have; by id; length want $100; if ^index(want,strip(alpha)) then want=catx(',',want,alpha); end; drop alpha; run; ... View more

Works perfect! ... View more

Merge Skill:   data have; input ID D_DATE :mmddyy10. AA BB CC DD; format D_DATE mmddyy10.; cards; 1 6/10/2015 5 4 0 0 1 7/10/2015 7 0 0 3 1 9/10/2015 0 0 3 0 1 6/10/2015 5 4 0 0 1 7/10/2015 7 0 0 3 1 9/10/2015 0 0 3 0 1 6/10/2015 5 4 0 0 1 7/10/2015 7 0 0 3 1 9/10/2015 0 0 3 0 1 6/10/2015 5 4 0 0 1 7/10/2015 7 0 0 3 1 9/10/2015 0 0 3 0 2 10/2/2015 0 1 0 3 2 10/30/2015 0 0 0 0 2 12/10/2015 0 3 0 0 3 1/8/2016 0 0 5 3 3 2/19/2016 0 2 0 0 4 3/11/2016 0 0 6 0 4 4/7/2016 0 2 0 0 5 5/11/2016 0 0 0 3 5 6/16/2016 0 0 0 0 ; data temp; set have; by id; if first.id then n=0; n+1; run; proc sql noprint nowarn; select distinct catt('temp(where=(n=',n,') rename=( d_date=d_date',n,' aa=aa',n,' bb=bb',n,' cc=cc',n,' dd=dd',n,'))') into : merge separated by ' ' from temp order by n; quit; data want; merge &merge; by id; drop n; run; ... View more

Works fantastic , thanks a lot ... View more

That makes perfect sense! I will be using this code as well. Thanks a ton! ... View more

If the data sets are sorted by id and date, as they are in your sample, then this is a good situation for conditional SET statements in a DATA step, as in:   data aa; input id order MMDDYY10.; format order MMDDYY10.; cards; 20 7/16/2016 20 10/2/2017 30 7/17/2016 38 7/18/2016 ; data bb; input id name $ received MMDDYY10.; format received MMDDYY10.; cards; 20 EG 7/26/2016 20 TP 7/26/2016 20 TP 10/22/2017 20 EG 10/22/2017 30 TP 8/1/2016 30 ER 8/1/2016 30 CD 8/1/2016 30 KR 8/1/2016 38 KR 8/3/2016 38 TP 8/3/2016 38 CD 8/3/2016 38 PI 8/3/2016 ; data want (drop=_date); set aa (rename=(order=_date) in=inaa) bb (rename=(received=_date) in=inbb); by id _date; if inaa then set aa; if inbb then set bb; if lag(inaa)=1 and inbb=1 and first.id=0; run; The first SET statement interleaves the two data sets by ID and _DATE (renamed from ORDER and RECEIVED respectively).  The conditional SETS re-read AA or BB, but with the order and received variables not renamed.  This means their values will not be replaced until another record from the same data set is read, i.e. RECEIVED and ORDER are "retained".    In turn, all you have to do is keep the instance in which the current record is from BB and the immediately preceding record is from AA.  The "and first.id=0" condition avoids instances in which one ID ends with an AA record, and the next ID begins with a BB record.   This is a good example of the robustness of the DATA step, requiring much less record comparison than SQL, at least when the data sets are sorted. ... View more

SQL is not very suited to the first task (first and last row), and even less suited to what you want now (create an unknown number of columns). 1. These 2 tasks are best performed in a data step 2. The structure you are requesting is ill-suited to any kind of processing. It's much better to keep the data vertical. ... View more