topic Data Merge Question in SAS Programming

Data Merge Question

newbi — Wed, 28 Mar 2012 18:47:25 GMT

Table A

Year	Qtr	Week	Sales
2012	1	14	15977700
2012	2	14	2458200

Table B

Year	Qtr	Week	Last Year Sales
2011	1	14	9873000
2011	2	14	4768200

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

Year	Qtr	Week	Sales	Last Year Sales
2012	1	14	15977700	9873000
2012	2	14	2458200	4756500

Data Merge Question

FriedEgg — Wed, 28 Mar 2012 19:10:34 GMT

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

Year	Qtr	Week	Sales
2012	1	14	15977700
2012	2	14	2458200

Table B

Year	Qtr	Week	Last Year Sales
2011	1	14	9873000
2011	2	14	4768200
2011	2	14	4756500

data test;

merge table_a table_b(drop=year);

by qtr week;

if last.week then output;

run;

Year	Qtr	Week	Sales	Last Year Sales
2012	1	14	15977700	9873000
2012	2	14	2458200	4756500

Re: Data Merge Question

newbi — Thu, 29 Mar 2012 14:50:22 GMT

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

Year	Qtr	Week	Last Year Sales
Missing	2	14	11700
2011	2	14	4756500

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

Year	Qtr	Week	Sales	Last Year Sales
2012	2	14	2458200	4756500

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