Divide 2 datasets values

[new_sas_user_4]

Hi folks, 

I have 2 datasets.

I would like to divide the rows from the first dataset /second based on the matching item/region:

eg Divide OUT audi AB by IN audi AB

so : 10/1 and 20/2

data Base1;
length metric $3 item $4 region $2;
input metric $ item $ region $ PY1 PY2 ;
datalines;
OUT audi AB 10 20
OUT audi ON 30 20
OUT BMW AB 50 20
OUT BMW ON 70 80
OUT BMW BC 90 100
;

data Base2;
length metric $3 item $4 region $2;
input metric $ item $ region $ PY1 PY2 ;
datalines;
IN audi AB 1 2
IN audi ON 3 2
IN BMW AB 5 2
IN BMW ON 7 8
IN BMW BC 9 10
;

Expected Output :

Div Audi AB 10 10

Div Audi ON 10 10

Div BMW AB 10 10 

IN BMW ON 10 10

IN BMW BC 10 10

Any ideas on how to achieve this?

[Reeza]

Merge the data sets first so that the values are in the same line and then do the division.

Merges in SAS
https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-in-sas/

Creating new variables:
https://stats.idre.ucla.edu/sas/modules/creating-and-recoding-variables-in-sas/

Spoiler

---

@new_sas_user_4 wrote:

Hi folks, 

I have 2 datasets.

I would like to divide the rows from the first dataset /second based on the matching item/region:

eg Divide OUT audi AB by IN audi AB

so : 10/1 and 20/2

data Base1;
length metric $3 item $4 region $2;
input metric $ item $ region $ PY1 PY2 ;
datalines;
OUT audi AB 10 20
OUT audi ON 30 20
OUT BMW AB 50 20
OUT BMW ON 70 80
OUT BMW BC 90 100
;

data Base2;
length metric $3 item $4 region $2;
input metric $ item $ region $ PY1 PY2 ;
datalines;
IN audi AB 1 2
IN audi ON 3 2
IN BMW AB 5 2
IN BMW ON 7 8
IN BMW BC 9 10
;

Expected Output :

Div Audi AB 10 10

Div Audi ON 10 10

Div BMW AB 10 10 

IN BMW ON 10 10

IN BMW BC 10 10

Any ideas on how to achieve this?

---

[PeterClemmensen · Accepted solution]

data want(drop=rc in:);
   if _N_=1 then do;
      declare hash h(dataset:'Base2(rename=(PY1=inPY1 PY2=inPY2))');
      h.definekey('item', 'region');
      h.definedata('inPY1', 'inPY2');
      h.definedone();
   end;
   
   set Base1;
   inPY1=.; inPY2=.;

   rc=h.find();

   PY1=divide(PY1, inPY1);
   PY2=divide(PY2, inPY2);
run;

[new_sas_user_4]

Thanks a lot for the solution!!

I want to understand declare hash/ definekey/definedata better ....Do you have any reference material I could read through to understand ? Highly appreciate the help!!

[PeterClemmensen]

I sure do 🙂

Obviously, use the Hash Object Documentation. 

If you really want to get an understanding of the hash object, get a hold of the book Data Management Solutions Using SAS® Hash Table Operations: A Business Intelligence Case Study.

Best of luck!