topic Re: how to tranpose mutiple colums in SAS Programming

how to tranpose mutiple colums

tekish — Fri, 23 Dec 2016 18:59:24 GMT

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

Re: how to tranpose mutiple colums

Tom — Fri, 23 Dec 2016 19:38:05 GMT

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;

Re: how to tranpose mutiple colums

Shmuel — Fri, 23 Dec 2016 19:39:36 GMT

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;

Re: how to tranpose mutiple colums

Reeza — Fri, 23 Dec 2016 20:24:48 GMT

@Shmuel wrote:

You don't need transpose, you can do it by datastep and retain:

@Shmuel but transpose is dynamic, you don't need to know the variable names ahead of time or how many they are. Otherwise, with RETAIN, you're forced to manually code these.

Re: how to tranpose mutiple colums

Shmuel — Fri, 23 Dec 2016 21:27:33 GMT

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