How to create a new row?

[dera]

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 !

[kiranv_ · Accepted solution]

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;

[dera]

Thank you Kiranv_v! It works perfectly.