topic Re: Using Proc Transpose in SAS Enterprise Guide

Using Proc Transpose

Sean_OConnor — Wed, 07 Jun 2017 11:38:26 GMT

Folks,

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

House_id	Person_id1	Person_id2	Person_id3	Person_id4	Person_id5	Person_id6	Person_id7	House_size	Adults	Kids
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

to this

Pesond_id	House_id	House_size	Adults	Kids
2826	1	7	3	4
3045	1	7	3	4
4585	1	7	3	4
1461	1	7	3	4
810	1	7	3	4
4465	1	7	3	4
3923	1	7	3	4
575	2	3	1	2
2472	2	3	1	2
3972	2	3	1	2
3185	3	6	2	4
1712	3	6	2	4
108	3	6	2	4
2420	3	6	2	4
2541	3	6	2	4
683	3	6	2	4

Any help would be most welcome.

Re: Using Proc Transpose

PaigeMiller — Wed, 07 Jun 2017 12:18:40 GMT

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;

Re: Using Proc Transpose

ballardw — Wed, 07 Jun 2017 14:37:17 GMT

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-data-AKA-generate/ta-p/258712 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.