topic Re: Transposing in SAS Programming

Transposing

sandrube — Tue, 04 Jan 2022 04:57:15 GMT

Wish everyone a Happy and Prosperous New Year!!!

I and trying to transpose the following data based on date.

data have;
input ID $ Vit_D CRP date :mmddyy10.;
format date mmddyy10.;
cards;
00-01 12 5.5 09/05/2021
00-01 15 1.6 10/12/2021
00-03 18 3.9 10/17/2021
00-03 26 3.8 09/16/2021
00-05 36 1.5 09/01/2021
00-05 30 0.8 10/20/2021
00-06 43 10.3 09/24/2021
00-06 52 0.8 10/25/2021
;

The outcome I am trying to get is following.

data have_1;
input ID $ Vit_D_1 CRP_1 date_1 :mmddyy10. Vit_D_2 CRP_2 date_2 :mmddyy10.;
format date_1 mmddyy10. date_2 mmddyy10.;
cards;
00-01 12 5.5 09/05/2021 15 1.6 10/12/2021
00-03 18 3.9 10/17/2021 26 3.8 09/16/2021
00-05 36 1.5 09/01/2021 30 0.8 10/20/2021
00-06 43 10.3 09/24/2021 52 0.8 10/25/2021
;

I am struggling because I am trying to do it based on dates. Could anyone help.

Thanks you very much!

Rube

Re: Transposing

andreas_lds — Tue, 04 Jan 2022 07:08:21 GMT

The structure of HAVE is almost always the preferred one, because it is easier to use in most procedures: you have to write less code. So the question arises: why do you need a wide dataset?

If you insist on transposing: You have to call proc transpose for each variable you want transposed.

Example:

proc transpose data=have out=t_date(drop=_name_) prefix=date_;
   by id;
   var date;
run;

And finally you have to merge all the created datasets.

If your dataset is large, the following step might perform better:


%macro transpose;
   %local maxCount;
   
   proc sql noprint;
      select max(count) into :maxCount trimmed
         from (
         select count(Id) as count
            from work.have 
               group by id
         );
   quit;


   data want;
      set have;
      by Id;
      
      length
         %do i = 1 %to &maxCount.;
            vit_d_&i. crp_&i. date_&i. 8
         %end;
      ;
      
      format date_: mmddyy10.;
      
      retain i vit_d_: crp_: date_:;
      
      array vits[&maxCount.] vit_d_1 - vit_d_&maxCount.;
      array crps[&maxCount.] crp_1 - crp_&maxCount.;
      array dates[&maxCount.] date_1 - date_&maxCount.;      
      
      if first.Id then do;
         call missing(of vit_d_:, of crp_:, of date_:);
         i = 1;
      end;
      
      vits[i] = vit_d;
      crps[i] = crp;
      dates[i] = date;
      
      i = i + 1;
      
      if last.Id then do;
         output;
      end;
      
      drop i crp vit_d date;
   run;
%mend;


%transpose;

Re: Transposing

Ksharp — Tue, 04 Jan 2022 11:56:01 GMT

data have;
input ID $ Vit_D CRP date :mmddyy10.;
format date mmddyy10.;
cards;
00-01 12 5.5 09/05/2021
00-01 15 1.6 10/12/2021
00-03 18 3.9 10/17/2021
00-03 26 3.8 09/16/2021
00-05 36 1.5 09/01/2021
00-05 30 0.8 10/20/2021
00-06 43 10.3 09/24/2021
00-06 52 0.8 10/25/2021
;

data temp;
set have;
by id;
if first.id then n=0;
n+1;
run;
proc sql noprint;
select distinct catt('temp(where=(n=',n,') rename=(Vit_D=Vit_D_',n,' CRP=CRP_',n,' date=date_',n,'))')
 into : merge separated by ' '
  from temp;
quit;
data want;
 merge &merge;
 by id;
 drop n;
run;

Re: Transposing

AndreaVianello — Wed, 05 Jan 2022 09:23:40 GMT

very elegant program !