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