Hi,
I'm struck again with one more logic. I have data as below.
ID | Flag | date | relative_month1 | relative_month2 | relative_month3 |
1 | 1 | 1.2014 | m1 | ||
1 | 0 | 2.2014 | m2 | ||
1 | 0 | 3.2014 | m3 | ||
1 | 1 | 4.2014 | m4 | m1 | |
1 | 0 | 5.2014 | m5 | m2 | |
1 | 0 | 6.2014 | m6 | m3 | |
1 | 1 | 7.2014 | m7 | m4 | m1 |
1 | 0 | 8.2014 | m8 | m5 | m2 |
1 | 0 | 9.2014 | m9 | m6 | m3 |
1 | 0 | 10.2014 | m10 | m7 | m4 |
2 | 1 | 2.2014 | m1 | ||
2 | 0 | 3.2014 | m2 | ||
2 | 0 | 4.2014 | m3 | ||
2 | 1 | 5.2014 | m4 | m1 | |
2 | 0 | 6.2014 | m5 | m2 | |
2 | 0 | 7.2014 | m6 | m3 | |
2 | 1 | 8.2014 | m7 | m4 | m1 |
2 | 0 | 9.2014 | m8 | m5 | m2 |
I need output as below.
201401 | 201402 | 201403 | 201404 | 201405 | 201406 | 201407 | 201408 | |
m1 | 1 | 1 | 1 | 1 | 1 | 1 | ||
m2 | 1 | 1 | 1 | 1 | 1 | 1 | ||
m3 | 1 | 1 | 1 | 1 | 1 | |||
m4 | 1 | 1 | 1 | 1 | 1 | |||
m5 | 1 | 1 | 1 | 1 | ||||
m6 | 1 | 1 | 1 | |||||
m7 | 1 | 1 | 1 | |||||
m8 | 1 | 1 | ||||||
m9 | 1 | |||||||
m10 | 1 | |||||||
m11 | ||||||||
m12 |
Here is how i want to group, for example ID 1 has M1 in 01.2014, 04.2014, 07.2014.
In 01.2014 it has 10 months follow up.
In 04.2014 it has 7 months follow up
In 07.2014 it has 4 months follow up. same thing is represented in the output.
I have millions of records like this, different ID's can have M1 on the same date.
I have taken cohort approach to solve this which is very lengthy.
Thanks alot in Advance. please let me know if something is not clear.
I edited the question as there was some error in the output given.
Please explain the entries for M2 in your desired output.
Art, CEO, AnalystFinder.com
Hi,
Please interpret the output vertically.
In input data ID 1 has three M1's. First M1 is starting on 01.2014 (relative_month1 variable) and has follow up of 10months.
Second M1 is starting on 04.2014 (relative_month2 variable) and has follow up of 7months.
Third M1 is starting on 07.2014(relative_month3 variable) and has follow up of 4months.
Same thing is represented in the output. Hope I clarified ur question.
ID | Flag | date | relative_month1 | relative_month2 | relative_month3 |
1 | 1 | 1.2014 | m1 | ||
1 | 0 | 2.2014 | m2 | ||
1 | 0 | 3.2014 | m3 | ||
1 | 1 | 4.2014 | m4 | m1 | |
1 | 0 | 5.2014 | m5 | m2 | |
1 | 0 | 6.2014 | m6 | m3 | |
1 | 1 | 7.2014 | m7 | m4 | m1 |
1 | 0 | 8.2014 | m8 | m5 | m2 |
1 | 0 | 9.2014 | m9 | m6 | m3 |
1 | 0 | 10.2014 | m10 | m7 | m4 |
No, I asked about M2
Art, CEO, AnalystFinder.com
This is relative month calculation.
ID 1 has M1 in 01.2014(relative_month1 variable) and M2 in 02.2014. I grouped this in 201401 bucket
ID 1 has M1 in in 02.2014(relative_month2 varibale) and M2 in 05.2014 - I grouped this in 201402 bucket
So M2 can fall on any date but we are not showing it in the output.
we are just showing when a particular patient has started and how many follow up's he has.
I will give another example if 10 patients are satrting on 01.2014(M1) and out of the 10, 9 are coming back second time(can be on any date), I will flag the second instance as M2 and represent 9 in M2. In the input data the complication is our initiations can also happen in between the follow up period. same patient can have multiple initiation dates, ID 1 has initiation dates on 01.2014, 04.2014, 07.2014.
our first initiation date is on 01.2014 when the follow up for this is M4, again the same ID is initiated also on 04.2014.
Sorry if this is confusing.
In the output I'm interested to show only the initiation date and the follow up's.
Unfortunately, it isn't at all clear to me. Hopefully, either someone else can undertand your description, or you can describe it better.
Art, CEO, AnalystFinder.com
Hi art297,
Thanks for your time and patience.
I have managed to code it though after spending considerable amount of time....)
Assuming I understand your question . data have; infile cards truncover expandtabs; input ID Flag (_date relative_month1 relative_month2 relative_month3) ($); date=input(cats('01.',_date),ddmmyy12.); format date yymmn6.; drop _date; cards; 1 1 1.2014 m1 1 0 2.2014 m2 1 0 3.2014 m3 1 1 4.2014 m4 m1 1 0 5.2014 m5 m2 1 0 6.2014 m6 m3 1 1 7.2014 m7 m4 m1 1 0 8.2014 m8 m5 m2 1 0 9.2014 m9 m6 m3 1 0 10.2014 m10 m7 m4 2 1 2.2014 m1 2 0 3.2014 m2 2 0 4.2014 m3 2 1 5.2014 m4 m1 2 0 6.2014 m5 m2 2 0 7.2014 m6 m3 2 1 8.2014 m7 m4 m1 2 0 9.2014 m8 m5 m2 ; run; data temp; set have(keep=id date relative_month1 rename=(relative_month1=month)) have(keep=id date relative_month2 rename=(relative_month2=month)) have(keep=id date relative_month3 rename=(relative_month3=month)); retain var 1; if not missing(month); run; data temp1(index=(month)); set temp; by id notsorted; retain first ; if first.id then first=date; format first yymmn6.; run; proc sort data=temp1 out=temp2 nodupkey; by id first; run; data _null_; set temp2 end=last; if _n_=1 then call execute(catt('data want; merge ')); call execute(catt('temp1(where=(id=',id,' and first=',first,') rename=(var=_',vvalue(first),'))')); if last then call execute(';by month;drop id date first;run;'); run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.