Transform the data

[lixuan]

Hello

I have data like this :

data temp;
input company$ time earning;
cards;
A 1993 45
A 1994 52
B 1993 42
B 1994 59
run;

and i would like to transform the data like this:

   1993 1994

A  45    52

B  42    59

How can I do it ? Thanks

[Shmuel · Accepted solution]

You can use next code:

proc transpose data=temp
     out=want(drop=_name_) prefix=Y ;
by company;
id time;
var earning; 
run;

[Jagadishkatam]

data temp;
input company$ time earning;
cards;
A 1993 45
A 1994 52
B 1993 42
B 1994 59
;
run;

proc sort data=temp;
by company time earning;
run;

proc transpose data=temp out=want;
by company;
id time;
var earning;
run;

[art297]

Not recommended to have variable names that only include numbers, since not all SAS procedures honor the validvarname=any option. However, that said, the following will do what you want.

Interestingly, in running the following SAS will produce warnings in the log that  _1993 and _1994 don't exist, but it does recode the variable names correctly:

data temp;
input company$ time earning;
cards;
A 1993 45
A 1994 52
B 1993 42
B 1994 59
;
run;

proc sort data=temp;
by company time earning;
run;

options validvarname=any;

proc transpose data=temp 
     out=want(drop=_name_ rename=(_1993='1993'n _1994='1994'n));
  by company;
  id time;
  var earning;
run;

Art, CEO, AnalystFinder.com

[lixuan]

Thank you for your patience with so simple problem.

[art297]

I stand/sit corrected regarding my previous response. When validvarname=any is in effect, proc transpose doesn't preface the new variables with underscores .. which is why the warning occurred when I included the rename option. Only the following was needed:

options validvarname=any;

proc transpose data=temp 
     out=want(drop=_name_);
  by company;
  id time;
  var earning;
run;

Art, CEO, AnalystFinder.com

[lixuan]

ok, thank u very much!