SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Transform data lines into variables

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Transform data lines into variables

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,


Accepted Solutions
Solution
‎11-10-2017 12:28 AM
Super User
Super User
Posts: 7,164

Re: Transform data lines into variables

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


All Replies
Super User
Posts: 20,252
Solution
‎11-10-2017 12:28 AM
Super User
Super User
Posts: 7,164

Re: Transform data lines into variables

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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