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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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
Tourmaline | Level 20

201601 is not a valid SAS Name for a dataset 🙂

Sir_Highbury
Quartz | Level 8

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
Super User

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

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
  • 4 replies
  • 1344 views
  • 2 likes
  • 4 in conversation