topic Re: 9.4 Proc transpose in SAS Programming

9.4 Proc transpose

Maisha_Huq — Tue, 23 Dec 2014 17:31:21 GMT

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!!!

Re: 9.4 Proc transpose

Reeza — Tue, 23 Dec 2014 17:49:38 GMT

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

Re: 9.4 Proc transpose

Maisha_Huq — Tue, 23 Dec 2014 17:56:36 GMT

So the picture above shows shows the original dataset structure.

The picture below is the desired dataset structure.

Re: 9.4 Proc transpose

Maisha_Huq — Tue, 23 Dec 2014 17:59:13 GMT

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

Re: 9.4 Proc transpose

Reeza — Tue, 23 Dec 2014 18:15:13 GMT

An answer to your question:

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

And a suggestion:

Re: 9.4 Proc transpose

Maisha_Huq — Tue, 23 Dec 2014 18:19:43 GMT

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!!

Re: 9.4 Proc transpose

Reeza — Tue, 23 Dec 2014 18:27:59 GMT

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

Re: 9.4 Proc transpose

Maisha_Huq — Tue, 23 Dec 2014 18:28:51 GMT

Ah, ok - thanks!

Re: 9.4 Proc transpose

Ksharp — Wed, 24 Dec 2014 06:09:59 GMT

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

Re: 9.4 Proc transpose

RamKumar — Wed, 24 Dec 2014 08:26:25 GMT

How does idgroup statement works in proc summary?

Re: 9.4 Proc transpose

Ksharp — Wed, 24 Dec 2014 09:26:12 GMT

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

Re: 9.4 Proc transpose

Reeza — Wed, 24 Dec 2014 14:20:31 GMT

http://www.sascommunity.org/wiki/Transpose_using_IDGROUP_in_PROC_SUMMARY