BookmarkSubscribeRSS Feed
newbi
SAS Employee

Table A

YearQtrWeekSales
201211415977700
20122142458200

Table B

YearQtrWeekLast Year Sales
20111149873000
20112144768200

Now when i merge table A and table B - the amount from table B for Qtr 2 doesn't show correct value.

Data test;

merge table A table B (drop=Year)

by Week

YearQtrWeekSalesLast Year Sales
2012114159777009873000
201221424582004756500
2 REPLIES 2
FriedEgg
SAS Employee

newbi,

Your data and code examples could not produce what you are seeing.  Your by statement will at least need to include the qtr variable in addition to the week, and there is no way I have ever seen that would alter the value of the 'last year sales' variable in a way you show.  I would assume you have something more like the following:

Table A

YearQtrWeekSales
201211415977700
20122142458200

Table B

YearQtrWeekLast Year Sales
20111149873000
20112144768200
20112144756500

data test;

merge table_a table_b(drop=year);

by qtr week;

if last.week then output;

run;

YearQtrWeekSalesLast Year Sales
2012114159777009873000
201221424582004756500
newbi
SAS Employee

Table A has the current year data and Table B has the last year data

And I actully have the data with missing values for table B

YearQtrWeekLast Year Sales
Missing21411700
20112144756500

So when i merge Table A with Table B and create query by Year, Qtr and Week i get following result:

YearQtrWeekSalesLast Year Sales
201221424582004756500

The last year sales should be 11700 + 4756500 = 4768200

I can default the missing year value to 2012 - which would fix the issue.  Is there any other way i can fix this without setting the default value ?

Data test;

merge table a table b (Drop=year);

by year, qtr, week;

if year =. then year = '2012';

Thanks

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
  • 2 replies
  • 795 views
  • 0 likes
  • 2 in conversation