BookmarkSubscribeRSS Feed
AmrAd
Obsidian | Level 7

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
4 REPLIES 4
Kurt_Bremser
Super User
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;
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
MarkusWeick
Barite | Level 11

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

Please help to keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Tom
Super User Tom
Super User

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

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1070 views
  • 3 likes
  • 5 in conversation