BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tekish
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Tom
Super User Tom
Super User

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;
Shmuel
Garnet | Level 18

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
Super User

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

Shmuel
Garnet | Level 18

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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