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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.