CODEWITHSUNDEEP
sas
Antony Kulchikovsky
Antony Kulchikovsky

Reputation: 13

SAS: divide table by another table

I am pretty new is SAS and now i am struggling with division table by table. Tables have the same size. My goal is to divide each element of table_1 by the corresponding element of table_2, producing a new table. Google advises to use SAS/IML but i have no access to it. Is there any option to do that in data step? Another ideas?

For instance first table looks like:

    30 30
    30 30

Second table:

    2 3
    5 6

Then output table should be:

    15 10
    6   5

Thank you a lot in advice!

Upvotes: 1

Views: 1145

Answers (2)

Tom
Tom

Reputation: 51581

You probably should store your data in a different way to make this problem easier (and most problems easier). Instead of storing a "matrix" where the row number and column number have some hidden meaning you could create a dataset where that meaning is stored into variables. So instead of:

30 30
30 30

You could have:

Row Col Value 
1   1   30
1   2   30
2   1   30
2   2   30

Just use meaningful names instead of ROW,COL and VALUE. Like COUNTRY, YEAR, POPULATION.

Then your division problem becomes both simpler and clearer. Combine the two datasets by the id variables and divide the variables to make a new variable.

data death_rate ;
  merge deaths population;
  by country year ;
  death_rate = deaths / population;
run;

If you do want to work with matrices then look into PROC IML. Here is example from documentation.

proc iml;
a = {1 2,
     3 4};
b = {5 6,
     7 8};
c = a/b;

Upvotes: 0

SAS2Python
SAS2Python

Reputation: 1297

One way to do would be merge the two tables together and perform the division.

data dividend;
  a = 30; b = 30; output;
  a = 30; b = 30; output;
run;

data divisor;
  c = 2; d = 3; output;
  c = 5 ; d = 6 ; output;
run;

data comb;
  merge dividend divisor;

  q1 = a/c;
  q2 = b/d;

  keep q1 q2;
run;

This assumes that there a 1 - to - 1 correspondence between the dividend and divisor rows

EDITED TO RESPOND TO QUESTION IN COMMENTS

Assuming you have years 2015 to 2010, each with 4 quarters, you could write a macro loop:


%macro divide;

%let years = %str(2015 2016 2017 2018 2019);
%let qtrs = %str(Q1 Q2 Q3 Q4);

data comb;
  merge dividend divisor;

  %let i = 1;
  %do %while (%scan(&years, &i) ne );

    %let year = %scan(&years, &i);
    %let j = 1;

    %do %while (%scan(&qtrs, &j) ne );
       %let q = %scan(&qtrs, &j);
       R_&q._&year = &q._&year._D / &q._&year._A;
       %let j = %eval(&j + 1);
    %end;

    %let i = %eval(&i + 1);
  %end;

  keep R_:;

run;

%mend;

%divide;

Upvotes: 1

Related Questions