rename variables dropping a prefix

[Sir_Highbury]

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.

[Kurt_Bremser · Accepted solution]

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.

[PeterClemmensen]

201601 is not a valid SAS Name for a dataset 🙂

[Sir_Highbury]

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. 

[ballardw]

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