Desktop productivity for business analysts and programmers

Merge Two data set or Nested Data set

Reply
New Contributor
Posts: 3

Merge Two data set or Nested Data set

[ Edited ]

HI,

 

      I have two data set and i want to search one data set to another when exact match is found increment 5% in both number_of_items and value, i know i can do it with merge but i am not able to do it because there four variable which is missing in any order. can i do it with the help of nested loop i tried but it giving me wrong result and gets merge which don't want. i need value from purchase data set only.

 

data sets

 

DATA product;
INFILE DATALINES DELIMITER=',' DSD;
INPUT k1 $ k2 $ k3 $ k4 $;
DATALINES;
63,,9141,1501
51,214,9280,,
57,,,1505
1,150,9172,,
58,214,,,
64,120,9666,,
,,,641
RUN;

 

 


DATA purchase;
INFILE DATALINES DELIMITER=',' DSD;
INPUT k1 $ k2 $ k3 $ k4 $ number_items value;
DATALINES;
63,,9141,1501,10,10
51,214,9280,1502,10,10
57,,,1505,10,10
1,150,9172,,10,10
58,214,9180,,10,10
59,214,9180,,10,10
64,120,9666,,10,10
RUN;

 

my approach if every value is present and need to find exact match.

 

data want;
    merge product(in=pro_in) purchase(in=pur_in);
           by key1 key2 key3 key4;

 

     if pro_in and pur_in then
           do;
              *perform action ;
       end;

 

but my below code will not work because of missing value so i tried this it is not giving me result

 

data want;
    merge product(in=pro_in) purchase(in=pur_in);
           by key1 key2 key3 key4;

 

if k1 ne '' and k2 ne '' and k3 ne '' and k4 ne '' then
do;

end;

 

else if k1 = '' and k2 ne '' and k3 ne '' and k4 ne '' then
do;
*perform action ;
end;

* so on different condition;
run;

 

product 

 

product.PNG

 

 

purchase

 

purchase.PNG

 

desire output

 

output.PNG

 

 

 

 

can i do it with nested loop or merge with given constraints? please provide hint or solution. 

Super User
Posts: 9,548

Re: Merge Two data set or Nested Data set

Please post example datasets that illustrate your issue. Post them in datasteps with datalines, so we can easily recreate your data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Merge Two data set or Nested Data set

Posted in reply to KurtBremser

Hi KurtBremser,

   I update my question with data sets.

Ask a Question
Discussion stats
  • 2 replies
  • 144 views
  • 0 likes
  • 2 in conversation