Hi everyone,
I have this data set with duplicate IDs:
data have;
format id $1. site $2. date mmddyy10. nlev $1.;
input id$ site$ date mmddyy8. nlev$;
cards;
A L1 01012001 1
A L3 02022002 2
B E3 03032003 1
B W3 04042004 2
B R4 05052005 3
;
run;
and I want to end up with one record per ID, but create new variables that will hold the values from the duplicate records to get something like this:
data want;
format id $1. site1 $2. date1 mmddyy10. site2 $2. date2 mmddyy10. site3 $2. date3 mmddyy10.;
input id$ site1$ date1 mmddyy8. site2$ date2 mmddyy8. site3$ date3 mmddyy8.;
cards;
A L1 01012001 L3 02022002 . .
B E3 03032003 W3 04042004 R4 05052005
;
run;
I created the NLEV variable to help me identify the duplicate IDs, but I don't need it in the final data set. Could someone give me some suggestions as to how I should go about doing this?
@bkq32 Hi and welcome to the SAS Community!
Provided that the nlev variable exists, you can do like this
data have;
format id $1. site $2. date mmddyy10. nlev $1.;
input id$ site$ date mmddyy8. nlev$;
cards;
A L1 01012001 1
A L3 02022002 2
B E3 03032003 1
B W3 04042004 2
B R4 05052005 3
;
run;
proc sql noprint;
select max(nlev) into :n from have;
quit;
%put &n.;
data want;
do _N_=1 by 1 until (last.id);
set have(rename=(date=_date site=_site));
by id;
array site{&n.} $2;
array date{&n.};
site[_N_]=_site;
date[_N_]=_date;
end;
keep id site: date:;
format date: mmddyyn8.;
run;
@bkq32 Hi and welcome to the SAS Community!
Provided that the nlev variable exists, you can do like this
data have;
format id $1. site $2. date mmddyy10. nlev $1.;
input id$ site$ date mmddyy8. nlev$;
cards;
A L1 01012001 1
A L3 02022002 2
B E3 03032003 1
B W3 04042004 2
B R4 05052005 3
;
run;
proc sql noprint;
select max(nlev) into :n from have;
quit;
%put &n.;
data want;
do _N_=1 by 1 until (last.id);
set have(rename=(date=_date site=_site));
by id;
array site{&n.} $2;
array date{&n.};
site[_N_]=_site;
date[_N_]=_date;
end;
keep id site: date:;
format date: mmddyyn8.;
run;
data have;
format id $1. site $2. date mmddyy10. nlev $1.;
input id$ site$ date mmddyy8. nlev$;
cards;
A L1 01012001 1
A L3 02022002 2
B E3 03032003 1
B W3 04042004 2
B R4 05052005 3
;
run;
proc sql;
create table level as
select distinct nlev from have;
quit;
data _null_;
set level end=last;
if _n_=1 then call execute('data want;merge ');
call execute(catt('have(where=(nlev="',nlev,'")
rename=(site=site_',nlev,' date=date_',nlev,'))'));
if last then call execute(';by id;drop nlev;run;');
run;
Thank you all!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.