Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

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 

 

product_test.jpg

 

purchase

 

purchase.jpg

 

 

               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

 

final_output.jpg

 

 

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;

 

 


Accepted Solutions
Solution
‎02-06-2018 12:34 AM
PROC Star
Posts: 1,309

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

Posted in reply to ramanandyadav
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;

View solution in original post


All Replies
Solution
‎02-06-2018 12:34 AM
PROC Star
Posts: 1,309

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

Posted in reply to ramanandyadav
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 199 views
  • 2 likes
  • 2 in conversation