I have 3 dataset with different information date, example:
DATASETNAME ID PAYMENTS INFORMATIONDATE
DS1 1 0 31Jan2016
DS1 2 1 31Jan2016
DS2 1 2 29Feb2016
DS2 2 1 29Feb2016
DS3 1 1 31Mar2016
DS3 2 1 31Mar2016
I want RESULT:
ID PAYMENTDS1 PAYMENTDS2 PAYMENTDS3 VECTOR
1 0 2 1 021
2 1 1 1 111
(using data step, not proc sql if is possible)
Look at PROC TRANSPOSE and then use a CATT to calculate vector.
Or can you guarantee that you'll have only 3 DS? I'm assuming this is a simplified example.
data have;
format DATASETNAME $4. informationdate date9.;
informat informationdate date9.;
input DATASETNAME $ ID PAYMENTS INFORMATIONDATE;
cards;
DS1 1 0 31Jan2016
DS1 2 1 31Jan2016
DS2 1 2 29Feb2016
DS2 2 1 29Feb2016
DS3 1 1 31Mar2016
DS3 2 1 31Mar2016
;
run;
proc sort data=have;
by id datasetname;
run;
proc transpose data=have out=flipped prefix=PAYMENTS;
by id;
id datasetname;
var payments;
run;
data want;
set flipped;
vector=catt(of payment:);
run;
Look at PROC TRANSPOSE and then use a CATT to calculate vector.
Or can you guarantee that you'll have only 3 DS? I'm assuming this is a simplified example.
data have;
format DATASETNAME $4. informationdate date9.;
informat informationdate date9.;
input DATASETNAME $ ID PAYMENTS INFORMATIONDATE;
cards;
DS1 1 0 31Jan2016
DS1 2 1 31Jan2016
DS2 1 2 29Feb2016
DS2 2 1 29Feb2016
DS3 1 1 31Mar2016
DS3 2 1 31Mar2016
;
run;
proc sort data=have;
by id datasetname;
run;
proc transpose data=have out=flipped prefix=PAYMENTS;
by id;
id datasetname;
var payments;
run;
data want;
set flipped;
vector=catt(of payment:);
run;
Thank you Reeza that works very good, just two more questions:
1. like pyments are numeric some obs are nulls so is a . and vector is like .1 .012 and my vector is char, how can I do to my vector is numeric?
2. how can I quit the var _NAME_ in mi dataset result?
thank you
1. What do you want it to look like? Repost sample data that accurately reflects your situation.
2. DROP _name_;
thank you Reeza I solved that situation
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.