BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bretthouston
Obsidian | Level 7

Hello,

 

I apologize for the very basic questions, but I've tried a few variations of proc transpose and I haven't been able to figure this one out.

 

Here is a sample of what my data set looks like:

PatientIDHospitalAdmissionDateTransfusion1DtmTransfusion1ProductTransfusion1Lot#Transfusion2DtmTransfusion2ProductTransfusion2Lot#Transfusion3DtmTransfusion3ProductTransfusion3Lot#
1HSC20OCT201821OCT2018:18:20:00RBCC05401679821OCT2018:22:00RBCC05401679922OCT2018:14:25:00HAS435710004
2HSC18OCT201818OCT2018:16:55:00PlateletC054015830      
3SBGH23OCT201826OCT2018:20:00:00RBCC05401477927OCT2018:23:43:00RBCC054014786   

 

This is what I would like it to look like:

PatientIDHospitalAdmissionDateTransfusionDtmTransfusionProductTransfusionLot#
1HSC20OCT201821OCT2018:18:20:00RBCC054016798
1HSC20OCT201821OCT2018:22:00:00RBCC054016799
1HSC20OCT201822OCT2018:14:25:00HAS435710004
2HSC18OCT201818OCT2018:16:55:00PlateletC054015830
3SBGH23OCT201826OCT2018:20:00:00RBCC054014779
3SBGH23OCT201827OCT2018:23:43:00RBCC054014786

 

Essentially, I would like the columns TransfusionDtm, TransfusionProduct and TransfusionLot# to collapse length wise.

 

Any guidance here would be very much appreciated. Thanks!!

 

Brett

 

1 ACCEPTED SOLUTION
11 REPLIES 11
ballardw
Super User

One way:

data want; 
   set have;
   array tdt    Transfusion1Dtm Transfusion2Dtm Transfusion3Dtm;
   array tpr    Transfusion1Product Transfusion2Product Transfusion3Product;
   array tlt    Transfusion1Lot Transfusion2Lot Transfusion3Lot;
   do i= 1 to dim(tdt);
      if not missing(tdt[i]) then do;
         transfusedt      = tdt[i];
         transfuseproduct = tpr[i];
         tranfuselot      = tlt[i];
         output;
     end;
   end;
   keep patientid hospital admissiondate 
        transfusedt  transfuseproduct tranfuselot ;
run;

 

The arrays are basically to simplify writing references to similar values.

 

With experience in SAS you will learn that naming similar variables with the number at the end such as instead of Transfusion1Dt use TransfusionDtm1 is nicer for coding. You can reference lists of similar variables with: TransfusionDtm1 - TransfusionDtm12 (if you had 12 measurements or such) or even as TransfusionDtm:  the : at the end that way references all variables that start with the same characters. 

May not seem like a big deal but wait until you start getting 50 similar variables. Those lists make things much nicer than having to type out all 50 names.

 

art297
Opal | Level 21

You've already marked this question as answered, but I had a couple of questions that I'd need answered before proposing what I think will be an easier solution.

 

1. Do you really have 3 variables named Transfusion1Lot#, Transfusion2Lot# and Transfusion3Lot# or are they called something else?

2. Your stated output file doesn't retain the 1s, 2s or 3s that were in the wide file. Do you need to keep them or are they irrelevant?

 

Art, CEO, AnalystFinder.com

 

bretthouston
Obsidian | Level 7

Hi Art, 

 

Thanks for your interest in this question - I'd love to hear what you think. The column name (in the dataset as it is) is transfusion_1_lot_num_cr, and there are actually 15 different transfusions captured (ie this extends from 1 to transfusion_15_lot_num_cr). These were unfortunately named by someone else.

 

You're correct that I don't need the # specified in the final column, I just want to make sure that the transfusion date/time, product and lot# move together in the table (if that makes sense).

 

Thanks,

Brett

art297
Opal | Level 21

Are the other two sets of variables named the same way. i.e. are they:

Transfusion1Dtm and Transfusion1Product

or

Transfusion_1_Dtm and Transfusion_1_Product etc.

or something else?

 

Art, CEO, AnalystFinder.com

 

bretthouston
Obsidian | Level 7

Hi Art,

 

The other variables are transfusion_1_time_cr (in dtm format) and transfusion_1_product_cr. 

 

Thx,

Brett

art297
Opal | Level 21

Are the variable names all lower case? In your original example, some characters in the variable names were upper case.

 

i.e., unless you indicate otherwise, my proposed solution will expect that the variables (case sensitive) will be in the form:

  input PatientID Hospital AdmissionDate
    transfusion_1_time_cr transfusion_1_product_cr transfusion_1_lot_num_cr
    transfusion_2_time_cr transfusion_2_product_cr transfusion_2_lot_num_cr
    transfusion_3_time_cr transfusion_3_product_cr transfusion_3_lot_num_cr;

etc, thru
transfusion_15_time_cr transfusion_15_product_cr transfusion_15_lot_num_cr;

Also, what is the wide dataset's actual filename?

 

Art, CEO, AnalystFinder.com

 

bretthouston
Obsidian | Level 7

The variables are lower case! And the file name is 'CRSummary' (in work library). 

 

Thanks,

Brett

art297
Opal | Level 21

In that case, I think that running the following will achieve what you want:

 

filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=CRSummary, out=want, by=PatientID Hospital AdmissionDate,
  prefix=transfusion_, suffix=_cr, id=num,
  var=_time _product _lot_num, var_first=no)

data want;
  set want (drop=num rename=(_time=time _product=product _lot_num=lot_num));
run;

It uses a SAS macro that a group of us wrote and presented at last year's SAS Global Forum. If you want to download the macro and/or read about it, you can get the paper, code, powerpoint and tip sheet at: https://github.com/art297/untranspose

 

Art, CEO, AnalystFinder.com

 

bretthouston
Obsidian | Level 7

Thank-you for your help! 🙂 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 4489 views
  • 2 likes
  • 4 in conversation