9.4 Proc transpose

Reply
Frequent Contributor
Posts: 131

9.4 Proc transpose

Hello, I have a dataset which I want to restructure: Currently, it has multiple records per ID and I would like it to be one record per ID. Currently, the dataset variables are: ID C_Number C_FirstName CLastName C_DOB C_Gender C_Relationship C_RS C_LW Each ID has multiple records if it has multiple valid values for C_number. So, right now ID 1234 may have multiple records for when C_number equals 1, 2, 3, and 4. For each C_number, the information recorded for the variables (ie C_FirstName CLastName C_DOB C_Gender C_Relationship C_RS C_LW) is different. So, ID 1234 will have a set of data recorded for C_FirstName, C_LastName, C_DOB, C_Gender, C_Relationship, C_RS, and C_LW and then a set of data for those variables in another record for ID 1234 for C_number 2. I would like to structure the dataset so it - has one record for ID 1234 and - the variables are renamed such that each C_number for each ID has its own set of [C_FirstName, C_LastName, C_DOB, C_Gender, C_Relationship, C_RS, and C_LW] variables named appropriately for it. Should I clarify? Proc transpose is the way? Thank  you!!!

Super User
Posts: 18,997

Re: 9.4 Proc transpose

Possibly, post sample data, input/what you have and output - what you're expecting as output.

Frequent Contributor
Posts: 131

Re: 9.4 Proc transpose

pic1.PNG

So the picture above shows shows the original dataset structure.

The picture below is the desired dataset structure.output.PNG

Frequent Contributor
Posts: 131

Re: 9.4 Proc transpose

This is the code I've written to do the proc transpose

code.PNG

Super User
Posts: 18,997

Re: 9.4 Proc transpose

An answer to your question:

SAS Learning Module: Reshaping data long to wide using the data step

And a suggestion:

Frequent Contributor
Posts: 131

Re: 9.4 Proc transpose


Thanks!  My first few messages have been short - I apologize!

Also, do you know how I can copy-paste my current code into the textboxes?  I'm never able to paste other text/my code directly into the box so I've ended up taking snapshots of these things and attaching them.

Thank you in advance!!

Super User
Posts: 18,997

Re: 9.4 Proc transpose

Dont use IE as your browser, or copy/paste to word then here.

Frequent Contributor
Posts: 131

Re: 9.4 Proc transpose

Ah, ok - thanks!

Super User
Posts: 9,854

Re: 9.4 Proc transpose

The simplest way is using proc means + idgroup if you didn't care about order of variables. otherwise I would use MERGE to transpose it .

data class;
 set sashelp.class;
run;
proc sort data=class;by sex;run;
proc sql;
 select max(n) into : max
  from (select count(*) as n from class group by sex);
quit;
proc summary data=class;
 by sex;
 output out=want idgroup(out[&max] (name age weight height)=);
run;

Xia Keshan

Regular Contributor
Posts: 168

Re: 9.4 Proc transpose

How does idgroup statement works in proc summary?

Super User
Posts: 9,854

Re: 9.4 Proc transpose

?? proc summary is exactly the same with proc means except not printing output .

Super User
Posts: 18,997

Re: 9.4 Proc transpose

Ask a Question
Discussion stats
  • 11 replies
  • 449 views
  • 6 likes
  • 4 in conversation