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

Hi all,

 

I am wondering how could I write the code as to transform the way the data is presented. Currently, I have the following:

 

Firm 1         0.001

Firm 1         0.023

Firm 1         0.002

....                 ....

Firm 4058   0.000

Firm 4058   0.001

Firm 4058   0.000

Firm 4058   0.001

 

I would like to transform the presentation such that the Firm 1 to 4058 are unique variables (columns) under which I have their returns:

 

Firm 1 ....   Firm 4058

0.001           0.000

0.023           0.001

0.002           0.000

                    0.001

 

I am a beginner in SAS and couldn't find examples on the internet. Hope my issue is explained clearly.

 

Many thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You probably need another variable to make it easy.

So add a ROW counter and then sort the data.

data fix ;
  set have ;
  by firm ;
  row+1;
  if first.firm then row=1;
run;

proc sort ;
  by row firm ;
run;

And now you can use PROC TRANSPOSE to flip it around.

proc transpose data=fix out=want ;
  by row ;
  id firm ;
  var value ;
run;
                                  Firm_
Obs    row    _NAME_    Firm_1     4058

 1      1     value      0.001     .000
 2      2     value      0.023     .001
 3      3     value      0.002     .000
 4      4     value       .        .001

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

You probably need another variable to make it easy.

So add a ROW counter and then sort the data.

data fix ;
  set have ;
  by firm ;
  row+1;
  if first.firm then row=1;
run;

proc sort ;
  by row firm ;
run;

And now you can use PROC TRANSPOSE to flip it around.

proc transpose data=fix out=want ;
  by row ;
  id firm ;
  var value ;
run;
                                  Firm_
Obs    row    _NAME_    Firm_1     4058

 1      1     value      0.001     .000
 2      2     value      0.023     .001
 3      3     value      0.002     .000
 4      4     value       .        .001

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1094 views
  • 0 likes
  • 3 in conversation