Hi, have the following data set which captures key process dates by an ID key....
ID_key | process | process_date |
1 | x | 01-01-15 |
1 | y | 02-01-15 |
2 | x | 03-01-15 |
and want to convert it to the below, where the process value becomes the name of the process date...
ID_key | x_process_date | y_process_date |
1 | 01-01-15 | 02-01-15 |
2 | 03-01-15 |
hope that made sense...
what would be the best way to do this ??
data have;
infile cards dsd;
informat id_key 1. process $1. process_date mmddyy10.;
format id_key 1. process $1. process_date mmddyy10.;
input ID_key process$ process_date;
cards;
1,x,01-01-15
1,y,02-01-15
2,x,03-01-15
;run;
proc transpose data=have out=tran_have(drop=process _name_)prefix=Process_Date_;by id_key process;id process;
data want(keep=id_key X_Process_date Y_Process_Date);
set tran_have(rename=process_date_y=Y_Process_date);
by id_key;
retain X_Process_date;
if first.id_key then X_Process_date = Process_Date_X;
if last.id_key;
format X_Process_Date mmddyy10.;
run;
By using suffix option instead of prefix you can skip the last datastep.
Check MERGE skill, me , Matt and Arthur.T proposed :
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
infile cards dsd;
informat id_key 1. process $1. process_date mmddyy10.;
format id_key 1. process $1. process_date mmddyy10.;
input ID_key process$ process_date;
cards;
1,x,01-01-15
1,y,02-01-15
2,x,03-01-15
;
run;
proc sql;
select distinct catt('have(where=(id_key=',id_key,' and process="',process,'") rename=(process_date=',process,'_process_date))') into : merge separated by ' '
from have;
quit;
data want;
merge &merge;
by id_key ;
drop process;
run;
data have;
infile cards dsd;
informat id_key 1. process $1. process_date mmddyy10.;
format id_key 1. process $1. process_date mmddyy10.;
input ID_key x process$ process_date;
cards;
1,1,x,01-01-15
1,2,y,02-01-15
2,1,x,03-01-15
;run;
proc transpose data=have out=tran_have(drop=_name_) suffix=_Process_Date;
by id_key ;
id process;
var process_date ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.