DATA Step, Macro, Functions and more

how to tranpose mutiple colums

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

how to tranpose mutiple colums

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


Accepted Solutions
Solution
‎12-25-2016 10:55 AM
Super User
Super User
Posts: 6,502

Re: how to tranpose mutiple colums

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;

View solution in original post


All Replies
Solution
‎12-25-2016 10:55 AM
Super User
Super User
Posts: 6,502

Re: how to tranpose mutiple colums

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;
Trusted Advisor
Posts: 1,399

Re: how to tranpose mutiple colums

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;

        

Super User
Posts: 17,905

Re: how to tranpose mutiple colums


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. 

Trusted Advisor
Posts: 1,399

Re: how to tranpose mutiple colums

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 200 views
  • 1 like
  • 4 in conversation