BookmarkSubscribeRSS Feed
Maisha_Huq
Quartz | Level 8

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

11 REPLIES 11
Reeza
Super User

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

Maisha_Huq
Quartz | Level 8

pic1.PNG

So the picture above shows shows the original dataset structure.

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

Maisha_Huq
Quartz | Level 8

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

code.PNG

Maisha_Huq
Quartz | Level 8


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

Reeza
Super User

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

Maisha_Huq
Quartz | Level 8

Ah, ok - thanks!

Ksharp
Super User

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

RamKumar
Fluorite | Level 6

How does idgroup statement works in proc summary?

Ksharp
Super User

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

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
  • 11 replies
  • 1527 views
  • 6 likes
  • 4 in conversation