topic Re: rename variables dropping a prefix in SAS Programming

rename variables dropping a prefix

Sir_Highbury — Mon, 21 Nov 2016 13:21:37 GMT

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.

Re: rename variables dropping a prefix

Kurt_Bremser — Mon, 21 Nov 2016 13:40:39 GMT

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.

Re: rename variables dropping a prefix

PeterClemmensen — Mon, 21 Nov 2016 13:41:40 GMT

201601 is not a valid SAS Name for a dataset 🙂

Re: rename variables dropping a prefix

ballardw — Mon, 21 Nov 2016 15:47:20 GMT

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

Re: rename variables dropping a prefix

Sir_Highbury — Mon, 21 Nov 2016 16:53:49 GMT

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.