DATA Step, Macro, Functions and more

Transform the data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 97
Accepted Solution

Transform the data

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


Accepted Solutions
Solution
‎03-15-2017 04:28 AM
Trusted Advisor
Posts: 1,584

Re: Transform the data

You can use next code:

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

View solution in original post


All Replies
Solution
‎03-15-2017 04:28 AM
Trusted Advisor
Posts: 1,584

Re: Transform the data

You can use next code:

proc transpose data=temp
     out=want(drop=_name_) prefix=Y ;
by company;
id time;
var earning; 
run;
Trusted Advisor
Posts: 1,137

Re: Transform the data

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;
Thanks,
Jag
PROC Star
Posts: 7,492

Re: Transform the data

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

 

Frequent Contributor
Posts: 97

Re: Transform the data

Thank you for your patience with so simple problem.

PROC Star
Posts: 7,492

Re: Transform the data

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

 

Frequent Contributor
Posts: 97

Re: Transform the data

ok, thank u very much!

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 191 views
  • 3 likes
  • 4 in conversation