Search one data set to another data set and create new data set based on operation peformed

ramanandyadav — Sun, 04 Feb 2018 16:14:23 GMT

Hi friends,

I have two data set and i want to search value of variables of each observation of data set to another data set and performed certain operation and create new data set or modified the current data set.

i have tried using merge but i failed and again i tried macro but it did not work as well. please help me to generate the required output. i have very huge data set in order to explain the question i have created very small data set.

All required data set is following

product_test

purchase

There are 2 data set product_test and purchase. i have to search old_prod_id and old_store_id of product_test data set with prod_id and store_id of purchase data set where match found replace prod_id and store_id with new_prod_id and new_store_id if it occur more than once then merge the observation and create new data set or modified purchase data set.

Desired result

DATA product_test;
INFILE DATALINES DELIMITER=',' DSD;
INPUT new_prod_id $ new_store_id $ old_prod_id $ old_store_id $;

DATALINES;
11,1,10,10
13,2,31,9
12,3,21,8
14,4,41,7
16,5,63,6
RUN;

DATA purchase;
INFILE DATALINES DELIMITER=',' DSD;
INPUT id $ prod_id $ store_id $ quantity value;

DATALINES;
1,10,10,10,100
2,31,10,4,10
3,31,9,3,100
4,31,9,12,100
5,21,8,6,100
6,63,6,5,100
7,21,8,3,100
8,41,7,2,100
9,21,8,1,100
10,63,9,5,100
RUN;

Re: Search one data set to another data set and create new data set based on operation peformed

novinosrin — Sun, 04 Feb 2018 20:23:47 GMT

DATA product_test;
INFILE DATALINES DELIMITER=',' DSD;
INPUT new_prod_id $ new_store_id $ old_prod_id $ old_store_id $;

DATALINES;
11,1,10,10
13,2,31,9
12,3,21,8
14,4,41,7
16,5,63,6
RUN;

 

 

DATA purchase;
INFILE DATALINES DELIMITER=',' DSD;
INPUT id $ prod_id $ store_id $ quantity value;

DATALINES;
1,10,10,10,100
2,31,10,4,10
3,31,9,3,100
4,31,9,12,100
5,21,8,6,100
6,63,6,5,100
7,21,8,3,100
8,41,7,2,100
9,21,8,1,100
10,63,9,5,100
RUN;


proc sort data=product_test out=_product_test(rename=(old_prod_id=prod_id old_store_id=store_id)) ;
by old_store_id old_prod_id ;
run;

proc sort data=purchase out=_purchase ;
by store_id prod_id ;
run;

data want;
merge _product_test(in=a) _purchase(in=b);
by store_id prod_id ;
if a and b then do;
if first.prod_id and first.store_id then _quant=0;
_quant+quantity;
prod_id=new_prod_id; store_id=new_store_id;
end;
if  a and b and last.prod_id;
drop new: id quantity;
run;

topic Re: Search one data set to another data set and create new data set based on operation peformed in SAS Enterprise Guide

Search one data set to another data set and create new data set based on operation peformed

Re: Search one data set to another data set and create new data set based on operation peformed