Desktop productivity for business analysts and programmers

Using Proc Transpose

Reply
Contributor
Posts: 43

Using Proc Transpose

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. 

 

Trusted Advisor
Posts: 1,770

Re: Using Proc Transpose

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;
Super User
Posts: 11,101

Re: Using Proc Transpose

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.

 

Ask a Question
Discussion stats
  • 2 replies
  • 100 views
  • 0 likes
  • 3 in conversation