Dear sir/madam;
I have a huge data set and trying to transpose the data and i have setup test dataset and the required out put are below.
data test;
input userid type $ sting $;
cards;
1 firstname John
1 lastname paul
1 tel 2222222222
1 title coor
1 Address 1243 doglas ave
2 firstname BOB
2 lastname JIM
2 tel 2334444444
2 title dr
2 address 4444 reag st.
3 firstname shar
3 lastname costa
3 tel 444444444
3 title RN
3 address 5453 first street
;
run;
required output.
userid | firstname | lastname | tel | title | Adddress |
1 | john | paul | 2222222222 | Cr. | 1243 douglas ave |
2 | BOB | JIM | 2334444444 | Dr. | 4444 reag st. |
3 | shar | costa | 444444444 | RN | 5453 first street |
thanks
Did you try PROC TRANSPOSE? Your data looks like a perfect example for that tool, assuming that your TYPE variable contains values that are valid SAS names.
proc transpose data=have out=want ;
by userid;
id type;
var string;
run;
Did you try PROC TRANSPOSE? Your data looks like a perfect example for that tool, assuming that your TYPE variable contains values that are valid SAS names.
proc transpose data=have out=want ;
by userid;
id type;
var string;
run;
You don't need transpose, you can do it by datastep and retain:
data want;
length firstname $20 lastname $20 tel $15 title $5 address $100; /* adapt length as need */
set test;
by id;
retain firstname lastname tel title address;
if first.id then call missing(firstname, lastname, tel, title, address);
string = lowcase(string);
if type = 'firstname' then firstname = strip(string); else
if type = 'lastname' then lastname = strip(string); else
if type = 'tel' then tel = strip(string); else
if type = 'title' then title = strip(string); else
if type = 'address' then address = strip(string); else put '>>>ERROR: Unknown ' string=;
if last.id then output;
drop type string;
run;
@Reeza, you are right but there are some benefits to the datstep:
- it enables check the IDs (type - in the example) list and catch mistypes or errors
- it enables to assign different names then those already in the ID (type)
- it enables conversion, concatenation or spliting variables in some new ones.
- it enables control on length and addimg labels and formats.
Maybe, in the that specific case, transpose is enough and gives a shorter code.
Then treat the datastep, just as one more possibility and be awuare of its benefits.
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.