BookmarkSubscribeRSS Feed
Sean_OConnor
Fluorite | Level 6

Folks,

 

I would like to use proc transpose to go from the following format;

 

House_idPerson_id1Person_id2Person_id3Person_id4Person_id5Person_id6Person_id7House_sizeAdultsKids
1282630454585146181044653923734
257524723972    312
33185171210824202541683 624
455393972     220
5191422794413575   422
64455513299318274519  532

 

to this 

 

Pesond_idHouse_idHouse_sizeAdultsKids
28261734
30451734
45851734
14611734
8101734
44651734
39231734
5752312
24722312
39722312
31853624
17123624
1083624
24203624
25413624
6833624

 

 

Any help would be most welcome. 

 

2 REPLIES 2
PaigeMiller
Diamond | Level 26

I don't think PROC TRANSPOSE is needed here, although it probably could work.

 

UNTESTED CODE

 

data want;
     set have;
     array person person_id1-person_id7;
     do i=1 to 7;
         person_id=person(i);
         if not missing(person(i)) then output;
     end;
     keep person_id house_id house_size adults kids;
run;
--
Paige Miller
ballardw
Super User

Please post data in form of a datastep.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Here's one way with proc transpose:

data have;
   input House_id Person_id1 Person_id2 Person_id3 Person_id4 Person_id5 Person_id6 Person_id7 House_size Adults Kids ;
datalines;
1 2826 3045 4585 1461 810 4465 3923 7 3 4 
2 575  2472 3972 .    .   .    .    3 1 2 
3 3185 1712 108 2420 2541 683  .    6 2 4 
4 5539 3972 .    .   .    .    .    2 2 0 
5 1914 2279 441 3575 .    .    .    4 2 2 
6 4455 513 2993 1827 4519 .    .    5 3 2 
;
run;


proc transpose data=have 
     out=want (drop=_name_ rename=(col1=Person_id) where=(not missing(Person_id)));
by house_id House_size Adults Kids;
var Person: ;
run;

which abuses by processing slightly and if your House_id is duplicated in the real data you will need to sort the data by house_id house_size adults kids prior to transpose.

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2 replies
  • 575 views
  • 0 likes
  • 3 in conversation