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.
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;
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.
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.
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.