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!
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.
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.
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;
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.