topic Re: Transform the data in SAS Programming

Transform the data

lixuan — Wed, 15 Mar 2017 02:01:25 GMT

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

Re: Transform the data

Shmuel — Wed, 15 Mar 2017 03:03:49 GMT

You can use next code:

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

Re: Transform the data

Jagadishkatam — Wed, 15 Mar 2017 03:05:47 GMT

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;

Re: Transform the data

art297 — Wed, 15 Mar 2017 04:13:00 GMT

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

Re: Transform the data

lixuan — Wed, 15 Mar 2017 08:31:35 GMT

Thank you for your patience with so simple problem.

Re: Transform the data

art297 — Wed, 15 Mar 2017 14:42:11 GMT

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

Re: Transform the data

lixuan — Wed, 15 Mar 2017 19:29:39 GMT

ok, thank u very much!