ratios between different tables

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

ratios between different tables

I would like to divide the data from the table "net_revenues"

by the data of the table "gross" (they are only exemples of my real data, that are very much bigger)

data net_revenues;

infile cards dlm=',';

input type $ '1'n '2'n '3'n;

cards;

a,18,12,100

b,20,23,50

c,22,1,75

; run;

data gross;

infile cards dlm=',';

input type $ '1'n '2'n '3'n;

cards;

a,180,120,1000

b,500,25,500

c,7000,16,750

; run;

With this statement I obtain the results from the column "1", but in my real data I have many columns, the names being numbers ( e.g. '1'n, '2'n, etc until '150'n) and I should use a large code

proc sql;

create table ratio as select a.'1'n/b.'1'n as ratio

from net_revenues a, gross b

where a.type=b.type;

QUIT;

Any idea on how to implents macros, array, do loop or everything to save code? thanks!


Accepted Solutions
Solution
‎10-26-2013 10:43 AM
PROC Star
Posts: 7,363

Re: ratios between different tables

Another option would be to use proc sql to write the code.  e.g.,

options validvarname=any;

data net_revenues;

  infile cards dlm=',';

  input type $ '1'n '2'n '3'n;

  cards;

a,18,12,100

b,20,23,50

c,22,1,75

;

data gross;

  infile cards dlm=',';

  input type $ '1'n '2'n '3'n;

  cards;

a,180,120,1000

b,500,25,500

c,7000,16,750

;

proc sql noprint;

  select "a.'"||trim(name)||

         "'n/b.'"||trim(name)||

         "'n as ratio_"||

         trim(name)

    into :ratios separated by ','

      from dictionary.columns

        where libname eq "WORK" and

              memname eq "NET_REVENUES"

              and name ne "type"

  ;

quit;

proc sql;

  create table ratio as

    select a.type,&ratios.

     from net_revenues a, gross b

       where a.type=b.type;

quit;

View solution in original post


All Replies
Super User
Posts: 17,813

Re: ratios between different tables

What does your output need to look like?

Contributor
Posts: 27

Re: ratios between different tables

It should be with the same data structure of the net_revenues and gross table, but with the results of the ratio.

Super Contributor
Posts: 644

Re: ratios between different tables

For the small data your code should be

proc sql;

     create table ratio as select

               a.'1'n/b.'1'n as ratio1

          ,    a.'2'n/b.'2'n as ratio2

          ,    a.'3'n/b.'3'n as ratio3

     from net_revenues a, gross b

     where a.type=b.type;

QUIT;

It would be possible to write a macro to generate all the select calculations for 150 columns

or you could just do what I did, copy one line 150 times and update the numbers in each line.  My bet is that would be quicker than writing the macro and testing and de-bugging it.  SQL is probably the best way to do what you want.

A datastep merge with your data structure poses a problem in that both tables have the same names and values from the second table would overwrite values with the same name in the first, so you would first have to rename all the columns in one table (which comes back to creating 150 similar statements).  If you did this you could define 3 arrays: one each to hold net and gross values and one for the ratios.

You could be forced back to using a macro to create individual lines in the SQL if the number of columns changes from time to time.

Richard

Super Contributor
Posts: 644

Re: ratios between different tables

Another option is to create the SQL rows in MS Excel and then paste them into your SQL

eg

populate column 1 with numbers 1 to 150

insert formula for rows 2 to 150

=" ,    a.'"&A2&"'n/b.'"&A2&"'n as ratio"&A2

for row 1 replace the comma with a space


Richard

Solution
‎10-26-2013 10:43 AM
PROC Star
Posts: 7,363

Re: ratios between different tables

Another option would be to use proc sql to write the code.  e.g.,

options validvarname=any;

data net_revenues;

  infile cards dlm=',';

  input type $ '1'n '2'n '3'n;

  cards;

a,18,12,100

b,20,23,50

c,22,1,75

;

data gross;

  infile cards dlm=',';

  input type $ '1'n '2'n '3'n;

  cards;

a,180,120,1000

b,500,25,500

c,7000,16,750

;

proc sql noprint;

  select "a.'"||trim(name)||

         "'n/b.'"||trim(name)||

         "'n as ratio_"||

         trim(name)

    into :ratios separated by ','

      from dictionary.columns

        where libname eq "WORK" and

              memname eq "NET_REVENUES"

              and name ne "type"

  ;

quit;

proc sql;

  create table ratio as

    select a.type,&ratios.

     from net_revenues a, gross b

       where a.type=b.type;

quit;

Super User
Super User
Posts: 6,499

Re: ratios between different tables

Why not just transpose them and then merge by your BY variable (TYPE) and the column name?

data net_revenues;

  input type $ var1-var3 @@ ;

cards;

a 18 12 100 b 20 23 50 c 22 1 75

run;

data gross;

  input type $ var1-var3 @@ ;

cards;

a 180 120 1000 b 500 25 500 c 7000 16 750

run;

proc transpose data=net_revenues out=numer(rename=(col1=numerator));

  by type;

run;

proc transpose data=gross  out=denom(rename=(col1=denomerator));

  by type;

run;

data ratio ;

  merge numer denom ;

  by type _name_;

  ratio = numerator / denomerator ;

run;


proc transpose data=ratio out=want ;

  by type ;

  id _name_;

  var ratio ;

run;


proc print; run;


Obs    type    _NAME_      var1      var2     var3

1      a      ratio     0.10000    0.1000     0.1

2      b      ratio     0.04000    0.9200     0.1

3      c      ratio     0.00314    0.0625     0.1

Super User
Super User
Posts: 6,499

Re: ratios between different tables

You can also create some extra variables and use two SET statements so that you do not need to worry about renaming the variables.

%let n=3 ;

data ratio ;

  set net_revenues;

  array value var1-var&n ;

  array temp  temp1-temp&n ;

  drop temp1-temp&n;

  do over temp ; temp = value ; end;

  set gross ;

  do over temp ; value = temp / value; end;

run;

Contributor
Posts: 27

Re: ratios between different tables

Hi Tom thanks but I have to keep my original variable names e.g. '1'n etc

Respected Advisor
Posts: 3,777

Re: ratios between different tables

An alternative, perhaps somewhat academic, would let data GROSS be scoring data for use with PROC SCORE. It takes a bit to get GROSS to a proper type=SCORE data but it remains mildly interesting.

data net_revenues;
   infile cards dlm=',';
  
input type $ '1'n '2'n '3'n;
   id +
1;
  
cards;
a,18,12,100
b,20,23,50
c,22,1,75
;;;;
  
run;
proc print;
  
run;

data gross;
   infile cards dlm=',';
  
input type $ '1'n '2'n '3'n;
  
array x
  • '1'n-'3'n;
       id +
    1;
      
    cards;
    a,180,120,1000
    b,500,25,500
    c,7000,16,750
    ;;;;
      
    run;
    proc print;
      
    run;
    proc transpose data=gross out=gross;
       by id type;
       var '1'n-'3'n;
      
    run;
    data score;
       set gross;
       col1 = divide(1,col1);
       retain _type_ 'SCORE';
      
    run;
    proc transpose data=score out=score;
       by id type _type_ _name_;
       id _name_;
       var col1;
       run;
    proc print;
      
    run;
    proc score data=net_revenues score=score out=ratio;
       by id;
       id type;
       var '1'n-'3'n;
      
    run;
    proc print;
      
    run;

    Respected Advisor
    Posts: 3,777

    Re: ratios between different tables

    The variables names pose no real problem.  Although I would just use RENAME.

    data net_revenues;
       infile cards dlm=',';
      
    input type $ '1'n '2'n '3'n;
      
    cards;
    a,18,12,100
    b,20,23,50
    c,22,1,75
    ;;;;
      
    run;
    proc print;
      
    run;

    data gross;
       infile cards dlm=',';
      
    input type $ '1'n '2'n '3'n;
      
    cards;
    a,180,120,1000
    b,500,25,500
    c,7000,16,750
    ;;;;
      
    run;
    proc print;
      
    run;

    data ratio;
       if 0 then set net_revenues;
       array ratio
  • _numeric_;
      
    merge net_revenues gross(rename=('1'n-'3'n=g1-g3));
       array g
  • g:;
  •    drop g:;
       do _n_ = 1 to dim(ratio);
          ratio[_n_] = divide(ratio[_n_],g[_n_]);
         
    end;
      
    run;
    proc print;
      
    run;
    Super User
    Super User
    Posts: 6,499

    Re: ratios between different tables

    So just use your original variable names.  The names of the variables are not important in the logic of the program.

    array value "1"n - "&n"n ;

    ☑ This topic is SOLVED.

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

    Discussion stats
    • 11 replies
    • 429 views
    • 3 likes
    • 6 in conversation