BookmarkSubscribeRSS Feed
Haritha1
Fluorite | Level 6

Hi,

 

I'm struck again with one more logic. I have data as below.

 

IDFlagdaterelative_month1relative_month2relative_month3
111.2014m1  
102.2014m2  
103.2014m3  
114.2014m4m1 
105.2014m5m2 
106.2014m6m3 
117.2014m7m4m1
108.2014m8m5m2
109.2014m9m6m3
1010.2014m10m7m4
212.2014m1  
203.2014m2  
204.2014m3  
215.2014m4m1 
206.2014m5m2 
207.2014m6m3 
218.2014m7m4m1
209.2014m8m5m2

 

I need output as below.

 

 201401201402201403201404201405201406201407201408
m111 11 11
m211 11 11
m311 11 1 
m411 11 1 
m511 11   
m611 1    
m711 1    
m811      
m91       
m101       
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.

8 REPLIES 8
art297
Opal | Level 21

Please explain the entries for M2 in your desired output.

 

Art, CEO, AnalystFinder.com

 

Haritha1
Fluorite | Level 6

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.

 

IDFlagdaterelative_month1relative_month2relative_month3
111.2014m1  
102.2014m2  
103.2014m3  
114.2014m4m1 
105.2014m5m2 
106.2014m6m3 
117.2014m7m4m1
108.2014m8m5m2
109.2014m9m6m3
1010.2014m10m7m4
art297
Opal | Level 21

No, I asked about M2

 

Art, CEO, AnalystFinder.com

 

Haritha1
Fluorite | Level 6

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.

 

 

Haritha1
Fluorite | Level 6
I hope it is clear.
art297
Opal | Level 21

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

 

Haritha1
Fluorite | Level 6

Hi art297,

 

Thanks for your time and patience.

 

I have managed to code it though after spending considerable amount of time....)

Ksharp
Super User
 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;
    

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1094 views
  • 0 likes
  • 3 in conversation