DATA Step, Macro, Functions and more

keeping one observation per drug per patient

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

keeping one observation per drug per patient

I have two datasets one represent med use before a specific data and one after. I coded the use of each med as 0/1. I want to combine the two dataset and create one dataset that tell me whether each id had at least one of each of the med before and another column that tell me wether they had at least one of the same med after

Here is my data 

dataset 1

id  drug   med_11pre    med_22pre  med_23pre    
1   11         1                  0                    0 
1    11        1                  0                    0
1    11        1                  0                    0
1    22        0                 1                    0
1   22         0                 1                    0
2   11          1                0                   0    
2   22         0                 0                    =0
2  23          0                 0                    1
2  23          0                0                    1 
2  25          0                0                    1

dataset 2
id  drug   med_11post    med_22post  med_23ost med_12post    
1   11         1                  0                    0                          0
1    12        0                  0                    0                          1
1    13       0                   0                    0                          0
1    22        0                 1                    0                           0
1   22         0                 1                    0                           0
2   11          1                0                   0                            0
2   22         0                 0                    0                        0
2  23          0                 0                    1                        0
2  23          0                0                    1                          0
2  25          0                0                    1                         0

 I want an output that look like this:
id med_11pre med_11post    med_12pre med12post
1               1            1               0                    1
2               1            1               0                    0

which mean give each id 0 if they have none during that period and 1 if they have any during that period. 
Thanks! 


 


Accepted Solutions
Solution
‎04-18-2016 02:20 PM
Trusted Advisor
Posts: 1,118

Re: keeping one observation per drug per patient

[ Edited ]
Posted in reply to lillymaginta

Hi @lillymaginta,

 

Try this:

proc summary data=have1; /* your "dataset 1" */
by id;
var med:;
output out=aggreg1(drop=_:) max=;
run;

proc summary data=have2; /* your "dataset 2" */
by id;
var med:;
output out=aggreg2(drop=_:) max=;
run;

data want;
merge aggreg:;
by id;
run;

If the column order in dataset WANT is important to you, we'll have to add code to rearrange the variables. 

View solution in original post


All Replies
Solution
‎04-18-2016 02:20 PM
Trusted Advisor
Posts: 1,118

Re: keeping one observation per drug per patient

[ Edited ]
Posted in reply to lillymaginta

Hi @lillymaginta,

 

Try this:

proc summary data=have1; /* your "dataset 1" */
by id;
var med:;
output out=aggreg1(drop=_:) max=;
run;

proc summary data=have2; /* your "dataset 2" */
by id;
var med:;
output out=aggreg2(drop=_:) max=;
run;

data want;
merge aggreg:;
by id;
run;

If the column order in dataset WANT is important to you, we'll have to add code to rearrange the variables. 

Trusted Advisor
Posts: 1,118

Re: keeping one observation per drug per patient

Posted in reply to FreelanceReinhard

This is code to rearrange the variables in dataset WANT to ID, med11pre, med11post, med12pre, med12post, etc.

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK' & memname='WANT' /* Please replace WORK and WANT by your libname and dataset name. */
order by tranwrd(name,'r','a'); /* This is to sort "pre" before "post". */
quit;

data want;
retain &varlist;
set want;
run;
Frequent Contributor
Posts: 128

Re: keeping one observation per drug per patient

Posted in reply to FreelanceReinhard
Thank you FreelanceReinhard as always a lifesaver!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 227 views
  • 2 likes
  • 2 in conversation