Hi,
I have a data set containing approx. 20,000 observations. Each ID represents a medical patient that had a medical exam. Based on the diagnosis, a code was assigned. One patient can have many codes and many exams at different date.
It looks like this:
id | date | code1 | code2 | code3 |
1 | 2010-04-12 | 01.01 | 01.02 | |
1 | 2011-02-20 | 01.01 | 01.02 | |
1 | 2012-08-24 | 02.05 | 01.01 | 01.02 |
2 | 2012-08-19 | 01.01 | ||
2 | 2012-10-10 | 01.09 | 01.01 | |
2 | 2015-01-27 | 01.01 | 01.09 | |
3 | 2014-05-02 | 03.02 | ||
3 | 2014-12-16 | 01.01 | 03.02 | |
4 | 2010-03-28 | 03.05 |
I am asking for your help to create observation containing the code2 and code3 value. Basically, I want to tell SAS that "if the code2 variable is not empty, take what is in the code2 variable and put it a new observation with the related date."
id | date | code |
1 | 2010-04-12 | 01.01 |
1 | 2010-04-12 | 01.02 |
1 | 2011-02-20 | 01.01 |
1 | 2011-02-20 | 01.02 |
1 | 2012-08-24 | 02.05 |
1 | 2012-08-24 | 01.01 |
1 | 2012-08-24 | 01.02 |
2 | 2012-10-10 | 01.09 |
2 | 2012-10-10 | 01.01 |
2 | 2015-01-27 | 01.01 |
2 | 2015-01-27 | 01.09 |
3 | 2014-05-02 | 03.02 |
3 | 2014-12-16 | 01.01 |
3 | 2014-12-16 | 03.02 |
4 | 2010-03-28 | 03.05 |
Can anyone help me with that?
Thank you !
something like this using proc transpose or
data have;
infile datalines dlm='09'x missover;
input id date:yymmdd10. code1 code2 code3;
format date yymmdd10.;
datalines;
1 2010-04-12 01.01 01.02
1 2011-02-20 01.01 01.02
1 2012-08-24 02.05 01.01 01.02
2 2012-08-19 01.01
2 2012-10-10 01.09 01.01
2 2015-01-27 01.01 01.09
3 2014-05-02 03.02
3 2014-12-16 01.01 03.02
4 2010-03-28 03.05
;
proc sort data=have out=have1;
by id date;
run;
proc transpose data=have1 out=want(rename=(col1=code) drop =_name_ where=(code ne .));
by id date;
var code1 code2 code3;
run;
or by array
data want (drop = code1 code2 code3 i where=(code ne .));
set have;
array co(*) code1 code2 code3;
do i= 1 to dim(co) ;
code= co(i);
output;
end;
run;
something like this using proc transpose or
data have;
infile datalines dlm='09'x missover;
input id date:yymmdd10. code1 code2 code3;
format date yymmdd10.;
datalines;
1 2010-04-12 01.01 01.02
1 2011-02-20 01.01 01.02
1 2012-08-24 02.05 01.01 01.02
2 2012-08-19 01.01
2 2012-10-10 01.09 01.01
2 2015-01-27 01.01 01.09
3 2014-05-02 03.02
3 2014-12-16 01.01 03.02
4 2010-03-28 03.05
;
proc sort data=have out=have1;
by id date;
run;
proc transpose data=have1 out=want(rename=(col1=code) drop =_name_ where=(code ne .));
by id date;
var code1 code2 code3;
run;
or by array
data want (drop = code1 code2 code3 i where=(code ne .));
set have;
array co(*) code1 code2 code3;
do i= 1 to dim(co) ;
code= co(i);
output;
end;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.