DATA Step, Macro, Functions and more

rename variables dropping a prefix

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

rename variables dropping a prefix

Dear experts,

 

givedn the following two datasets:

data a;
input country: $12. month_201601: $25. month_201602: $25. month_201603: $25.;
datalines;
russia 20 30 40 50
belgium 0 0 0 0
germany 10 20 30 40
;run;

data b;
input country: $12. sales_201601: $25. sales_201602: $25. sales_201603: $25.;
datalines;
russia 20 30 40 50
belgium 0 0 0 0
germany 10 20 30 40
;run;

 

I need to merge the data into a unique data set. How can I adjust the variable names droppin the prefix, i.e. sales_201601 renamed as 201601 and month201601 as 201601. Afterward I could easily append them. Thanks in advance, SH.


Accepted Solutions
Solution
‎11-21-2016 11:51 AM
Super User
Posts: 6,932

Re: rename variables dropping a prefix

201601 is NOT a valid SAS name, it would be considered a numeric literal.

And why are you reading numeric values into character variables?

Once again, the benefits of a long vs. wide format come into play:

input country: $12. month_201601 month_201602 month_201603;
datalines;
russia 20 30 40 50
belgium 0 0 0 0
germany 10 20 30 40
;
run;

data b;
input country: $12. sales_201601 sales_201602 sales_201603;
datalines;
russia 20 30 40 50
belgium 0 0 0 0
germany 10 20 30 40
;
run;

proc transpose data=a out=a1;
by country notsorted;
run;

proc transpose data=b out=b1;
by country notsorted;
run;

data want;
set a1 b1;
_name_ = scan(_name_,2,'_');
rename col1=sales _name_=month;
run;

You can now use by-group processing to do analyses.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎11-21-2016 11:51 AM
Super User
Posts: 6,932

Re: rename variables dropping a prefix

201601 is NOT a valid SAS name, it would be considered a numeric literal.

And why are you reading numeric values into character variables?

Once again, the benefits of a long vs. wide format come into play:

input country: $12. month_201601 month_201602 month_201603;
datalines;
russia 20 30 40 50
belgium 0 0 0 0
germany 10 20 30 40
;
run;

data b;
input country: $12. sales_201601 sales_201602 sales_201603;
datalines;
russia 20 30 40 50
belgium 0 0 0 0
germany 10 20 30 40
;
run;

proc transpose data=a out=a1;
by country notsorted;
run;

proc transpose data=b out=b1;
by country notsorted;
run;

data want;
set a1 b1;
_name_ = scan(_name_,2,'_');
rename col1=sales _name_=month;
run;

You can now use by-group processing to do analyses.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 551

Re: rename variables dropping a prefix

201601 is not a valid SAS Name for a dataset Smiley Happy

Frequent Contributor
Posts: 127

Re: rename variables dropping a prefix

so based on your understanding the issue was to find a valid SAS name for a dataset? Thanks for your effort but please avoid to spam the forum answering if you can contribute to solve the issue. 

Super User
Posts: 10,485

Re: rename variables dropping a prefix

I would be interested in see the Proc Contents result run against the data set you are going to append this data set to.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 205 views
  • 2 likes
  • 4 in conversation