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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.