Merge two datasets in a DO loop

5 pts.
Tags:
DO LOOP statement
MERGE statement
SAP Business Intelligence
SAS
SAS Business Intelligence software
SAS Data Analysis
I have two datasets like this:

data1 :

store sales1 sales2....sales52

1

2

3

.

.

10

data2:

Store sales1 sales2.....sales52

 1

2

3

.

.

.

100

 

okay, now what I want is to take the first row of data1 merge it with data2, take the difference of sales between the observation from data1 and all the rows of data2;

specifically I want sales1 (of the store from data1)-sales1(all other stores in data2) then sales2- sales2...., then I want to to print this out on a different dataset,

And the MOst important part is I want to do this step for every store in data1, seperately. Like after doing it for the first row of data1 I want to do the same for the second row of data1 and so on...

 So that at last if I have 10 stores in data1, I will have 10 different new datasets, showing the differences betwwen sales of each store from data1 and all the stores in data2. I would be glad if anyone can help me on this. Thanks!



Software/Hardware used:
SAS
ASKED: August 27, 2010  3:44 PM
UPDATED: September 21, 2011  10:52 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

data one;
input store sale1 sale2 sale3 sale4 sale5;
cards;
1 100 200 300 400 500
2 150 250 350 450 550
;
run;
data two;
input store sale1 sale2 sale3 sale4 sale5;
cards;
1 200 300 400 500 600
2 250 350 450 550 650
3 380 370 840 560 350
4 230 340 530 358 560
5 350 990 880 770 670
6 240 120 320 430 350
7 340 670 870 560 410
8 350 450 710 410 230
9 723 540 720 240 150
10 238 450 130 140 540
11 200 300 400 500 600
12 250 350 450 550 650
13 380 370 840 560 350
14 230 340 530 358 560
15 350 990 880 770 670
16 240 120 320 430 350
17 340 670 870 560 410
18 350 450 710 410 230
19 723 540 720 240 150
20 238 450 130 140 540
;
run;
data k;
set one;
rename sale1-sale5=sale10-sale15;
run;

data new;
merge two(in=a) k(in=b);
by store;
if a;
run;
proc print;
run;
%macro test(dsn,dsn1,out,var,with);
proc compare base=&dsn compare=&dsn1 out=&out;
var &var;
with &with;
run;
%mend test;
%test(new,new,result1,sale1,sale10);
%test(new,new,result2,sale2,sale11);
%test(new,new,result3,sale3,sale12);
%test(new,new,result4,sale4,sale13);
%test(new,new,result5,sale5,sale14);

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following