topic Re: Transpose in SAS Programming

Transpose

AmrAd — Sun, 21 May 2023 07:52:04 GMT

Hi All,

I have a table as follows

F1	F2
ACC_STATUS	1
ACC_STATUS	1
ACC_STATUS	1
ACC_STATUS	3
ACC_STATUS	1
ACC_STATUS_DESC	Closed
ACC_STATUS_DESC	Active
ACC_STATUS_DESC	Active
ACC_STATUS_DESC	Active
ACC_STATUS_DESC	Active

and i need below output

ACC_STATUS	ACC_STATUS_DESC
1	Closed
1	Active
1	Active
3	Active
1	Active

Re: Transpose

Kurt_Bremser — Sun, 21 May 2023 09:00:59 GMT

data want;
merge
  have (
    rename=(f2=acc_status)
    where=(f1 = "ACC_STATUS")
  )
  have (
    rename=(f2=acc_status_desc)
    where=(f1 = "ACC_STATUS_DESC")
  )
;
drop f1;
run;

Re: Transpose

PaigeMiller — Sun, 21 May 2023 09:02:57 GMT

This doesn't seem like a transpose to me. This ought to work (although I have made a lot of assumptions)

data want;
    merge have(where=(f1='ACC_STATUS') rename=(f2=acc_status))
        have(where=(f1='ACC_STATUS_DESC') rename=(f2=acc_status_desc));
    drop f1;
run;

Re: Transpose

MarkusWeick — Sun, 21 May 2023 09:43:57 GMT

Yes, it looks rather like a table transformation from long format to wide format, than a Transpose.

Re: Transpose

Tom — Sun, 21 May 2023 17:59:30 GMT

To use PROC TRANSPOSE you will need to have another variable that indicates which rows go together.

data have;
  row+1;
  input f1 :$32. f2 :$32. ;
  if f1 ne lag(f1) then row=1;
cards;
ACC_STATUS 1
ACC_STATUS 1
ACC_STATUS 1
ACC_STATUS 3
ACC_STATUS 1
ACC_STATUS_DESC Closed
ACC_STATUS_DESC Active
ACC_STATUS_DESC Active
ACC_STATUS_DESC Active
ACC_STATUS_DESC Active
;

Then you can use that as a BY variable.

proc sort;
  by row f1 ;
run;

proc transpose data=have out=want(drop=_name_);
  by row;
  id f1;
  var f2;
run;

Result

               ACC_     ACC_STATUS_
Obs    row    STATUS       DESC

 1      1       1         Closed
 2      2       1         Active
 3      3       1         Active
 4      4       3         Active
 5      5       1         Active