BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ramanandyadav
Fluorite | Level 6

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

1 REPLY 1
novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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