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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

6 REPLIES 6
Shmuel
Garnet | Level 18

You can use next code:

proc transpose data=temp
     out=want(drop=_name_) prefix=Y ;
by company;
id time;
var earning; 
run;
Jagadishkatam
Amethyst | Level 16
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
art297
Opal | Level 21

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
Obsidian | Level 7

Thank you for your patience with so simple problem.

art297
Opal | Level 21

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
Obsidian | Level 7

ok, thank u very much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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