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