DATA Step, Macro, Functions and more

Data Merge Question

Reply
SAS Employee
Posts: 73

Data Merge Question

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
Trusted Advisor
Posts: 1,300

Data Merge Question

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
SAS Employee
Posts: 73

Re: Data Merge Question

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

Ask a Question
Discussion stats
  • 2 replies
  • 205 views
  • 0 likes
  • 2 in conversation