topic Re: Transpose Data; Multiple Observations into one row per subject in SAS Programming

Transpose Data; Multiple Observations into one row per subject

sassy_lm — Thu, 07 Nov 2019 18:24:07 GMT

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

Re: Transpose Data; Multiple Observations into one row per subject

art297 — Thu, 07 Nov 2019 18:43:14 GMT

Are BID, Dx_year and dx_code all numeric variables, or are some character variables?

Art, CEO, AnalystFinder.com

Re: Transpose Data; Multiple Observations into one row per subject

sassy_lm — Thu, 07 Nov 2019 19:31:55 GMT

BID and Dx_year are numeric variables. However, the variable dx_code is a character variable.

Thanks for asking! Happy to answer any other questions.

- Liz

Re: Transpose Data; Multiple Observations into one row per subject

hashman — Thu, 07 Nov 2019 20:08:36 GMT

@sassy_lm:

Read the file to find out what it the largest [id,bid] group on file
Use the result to size up arrays and create the requisite number of array variables for the output
Read the file again by [id,bid] (it's assumed sorted accordingly) and populate the arrays using the record number in each BY group as an index, starting for each BY group at 1

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:

The implicit OUTPUT before RUN writes out the array variables populated through the iterations of the DoW-loop as one record.
Then program control is passed back to the top of the step and the array variables are auto-populated with missing values since they are not retained.
You can kill the %PUT statement. It's just FYI.

Kind regards

Paul D.

Re: Transpose Data; Multiple Observations into one row per subject

art297 — Thu, 07 Nov 2019 22:58:21 GMT

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

Re: Transpose Data; Multiple Observations into one row per subject

ballardw — Thu, 07 Nov 2019 20:32:06 GMT

And how is that data set to be used?