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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.