Hi everyone,
I am wondering how I can transpose the data below which currently has multiple rows per subject (Table 1). I would like to create a new dataset with one row per subject (Table 2). The variables with multiple obs are associated with ID and BID. Thank you in advance! - Liz
Table 1:
ID | BID | Dx_year | dx_code | short_description |
1 | 250 | 2000 | 12345 | HTN |
1 | 250 | 2000 | 12344 | obesity |
1 | 250 | 2000 | 12333 | vomiting |
2 | 870 | 2002 | 12223 | TB |
2 | 870 | 2002 | 14322 | anemia |
2 | 870 | 2003 | 12355 | tobacco use |
2 | 870 | 2003 | 14325 | infection |
Table 2:
ID | BID | Dx_year1 | Dx_year2 | Dx_year3 | Dx_year4 | dx_code1 | dx_code2 | dx_code3 | dx_code4 | short_description1 | short_description2 | short_description3 | short_description4 |
1 | 250 | 2000 | 2000 | 2000 | 12345 | 12344 | 12333 | HTN | obesity | vomiting | |||
2 | 870 | 2002 | 2002 | 2003 | 2003 | 12223 | 14322 | 12355 | 14325 | TB | anemia | tobacco use | infection |
Are BID, Dx_year and dx_code all numeric variables, or are some character variables?
Art, CEO, AnalystFinder.com
In other (i.e. SAS) words:
data have ;
input ID BID Dx_year dx_code $ short_description & :$12. ;
cards ;
1 250 2000 12345 HTN
1 250 2000 12344 obesity
1 250 2000 12333 vomiting
2 870 2002 12223 TB
2 870 2002 14322 anemia
2 870 2003 12355 tobacco use
2 870 2003 14325 infection
;
run ;
proc sql noprint ;
select cats (max (g)) into :g from (select count (*) as g from have group id, bid) ;
quit ;
%put &=g ;
data want (drop = dx_year dx_code short_description) ;
do _i_ = 1 by 1 until (last.bid) ;
set have ;
by id bid ;
array dxy dx_year1-dx_year&g ;
array dxc $ 8 dx_code1-dx_code&g ;
array sd $12 short_description1-short_description&g ;
dxy = dx_year ;
dxc = dx_code ;
sd = short_description ;
end ;
run ;
Note:
Kind regards
Paul D.
My suggestion would be to use the macro a group of us wrote and presented at SAS Global Forum a couple of years ago.
One way to do that would be to run the following code:
filename ut url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include ut ;
%transpose(data=have, out=want, by=ID BID, Guessingrows=1000,
var=dx_year dx_code short_description)
If you want to see or download the actual macro and/or the paper, you can find it at: https://github.com/art297/transpose
Art, CEO, AnalystFinder.com
And how is that data set to be used?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.