DATA Step, Macro, Functions and more

How do I efficiently divide all numeric variables in my dataset against all other numeric variables?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How do I efficiently divide all numeric variables in my dataset against all other numeric variables?

 

 

Hi,

 

How can I write a macro to efficiently divide all of the numeric variables in my dataset against

all other numeric variables in my dataset?  For example:

 

Var1 Var2 Var3

1.       2.     4

 

Would yield:

 

Var1divVar2 = .5

Var1divVar3 = .25

 

Thanks!!


Accepted Solutions
Solution
‎03-07-2016 02:29 PM
Respected Advisor
Posts: 4,659

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

[ Edited ]

Straightforward programming, no macros :

 

data have;
input id $ a b c d e;
datalines;
Ballard 1 2 3 4 5 
Reeza 6 7 8 9 0 
Alan 2 3 4 5 .
;

data long;
set have;
array _num _numeric_;
obs = _n_;
do vorder = 1 to dim(_num);
    vname = vname(_num{vorder});
    value = _num{vorder};
    output;
    end;
keep _character_ obs vorder value;
run;

proc sql;
create table longRatios as
select 
    num.obs,
    num.id,
    cats(num.vname, "_div_", div.vname) as ratioName,
    case
        when div.value = 0 then .
        when missing(num.value) then .
        when missing(div.value) then .
        else num.value / div.value 
        end as ratio format=5.2
from 
    long as num inner join 
    long as div on num.obs=div.obs and num.vname ne div.vname
order by obs, num.vorder, div.vorder;
quit;

proc transpose data=longRatios out=ratios(drop=_name_ obs);
by obs id;
var ratio;
id ratioName;
run;

proc print data=ratios noobs; run;

 

PG

View solution in original post


All Replies
Super User
Posts: 17,912

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

Can you provide some more sample data and output?

 

I'm curious as to what the logic is behind this.

 

Also, do you have SAS IML, this sounds a lot like a matrix operation.

New Contributor
Posts: 4

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

Hi 

 

really nothing more to describe.

i just need to create ratios between each and every variable in a large dataset.

 

I'd appreciate any advice on how to do this efficiently.

 

thanks!,

Super User
Posts: 10,538

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

Is this between values only on one row (observation) of a data set or are you looking to do this calculation across rows as well?

 

If the last is what you want I suggest that provide an example with 3 or 4 variables, 3 or 4 rows of input data and what you expect the output to look like.

 

If you have currently 10 variables then you are going to end up with 45 new variables. Is that what you intend?

New Contributor
Posts: 4

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

i don't have iml

Super User
Posts: 5,093

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

[ Edited ]

First, you need to do your homework.  Do you want Var1DivVar2 as well as Var2DivVar1?  If the denominator is 0, what would you like the result to be?  If either the numerator or denominator is missing, what would you like the result to be?  If you are starting with 100 numeric variables, are you prepared to end up with a data set with 10,000 variables?  

 

What variable names will you use to hold the results?  If you start with variable names that are 20 characters long, you won't have room to create a new variable name that is 40 characters long.  So how will you map the ratios to the variables that went into the calculations?

 

Truthfully, the programming is fairly straightforward and may not even involve macros.  It's the preparation that is difficult.

Solution
‎03-07-2016 02:29 PM
Respected Advisor
Posts: 4,659

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

[ Edited ]

Straightforward programming, no macros :

 

data have;
input id $ a b c d e;
datalines;
Ballard 1 2 3 4 5 
Reeza 6 7 8 9 0 
Alan 2 3 4 5 .
;

data long;
set have;
array _num _numeric_;
obs = _n_;
do vorder = 1 to dim(_num);
    vname = vname(_num{vorder});
    value = _num{vorder};
    output;
    end;
keep _character_ obs vorder value;
run;

proc sql;
create table longRatios as
select 
    num.obs,
    num.id,
    cats(num.vname, "_div_", div.vname) as ratioName,
    case
        when div.value = 0 then .
        when missing(num.value) then .
        when missing(div.value) then .
        else num.value / div.value 
        end as ratio format=5.2
from 
    long as num inner join 
    long as div on num.obs=div.obs and num.vname ne div.vname
order by obs, num.vorder, div.vorder;
quit;

proc transpose data=longRatios out=ratios(drop=_name_ obs);
by obs id;
var ratio;
id ratioName;
run;

proc print data=ratios noobs; run;

 

PG
Respected Advisor
Posts: 3,777

Re: How do I efficiently divide all numeric variables in my dataset against all other numeric variab

divide(num.value,div.value) as ratio format 5.2

Maybe

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 557 views
  • 2 likes
  • 6 in conversation