CODEWITHSUNDEEP
sqlsas
Beanie
Beanie

Reputation: 41

SAS If then else Statement with Do loop

I need to get a percentage for 75 values in 75 columns individually. And I want to use a do loop so I don't have to hard code it 75 times. There are some conditions so there will be a where statement.

I am not getting the do loop correctly but I am using the below to get a percentage

case when (SUM(t1.sam)) >0 then
  ((SUM(t1.sam))/(SUM(t1.sam_Threshold)))*100
else 0
end

I tried the below and its a bit better:

data test;
i_1=4;
i_2=8;
i_3=4;
i_4=8;
V_ANN_V_INSP=24;
run;

%macro loop(); 
%let numcols=4; 
proc sql;
create table test3 as
select V_ANN_V_INSP,
%do i=1 %to &numcols;
(i_&i/V_ANN_V_INSP)*100 as i_&i._perc
%if &i<&numcols %then %do;,
%end;
%end;
from test;
quit;
%mend; 
%loop();

Upvotes: 0

Views: 6673

Answers (1)

Joe
Joe

Reputation: 63424

CASE WHEN is a SQL statement, not a data step statement, so you can't use a DO loop there. Depending on what you're doing exactly, there are a lot of possible solutions here. Posting additional code would help to get a more precise answer, but I can give you a few suggestions.

First, take it into a data step. Then you can use a do loop.

data want;
set have;
array nums sam1-sam75;
array denoms threshold1-threshold75;
array pct[75];
do _t = 1 to dim(nums);
  pct[_t]=nums[_t]/denoms[_t];
end;
run;

Second, if you need to do this in SQL for some reason, you can write out the SQL code either in a macro or in a data step in a pre-processing step.

%macro do_sql_st;
  %do _t = 1 to 75;
    case when (SUM(t1.sam&_t.)) >0 then
  ((SUM(t1.sam&_t.))/(SUM(t1.sam_Threshold&_t.)))*100
else 0
end
as pct&_t.
%end;
%mend do_sql_st;

proc sql;
select %do_sql_st from t1 where ... ;
quit;

These are not terribly flexible; unless you have very specifically named variables, they won't work as is. You're more likely to want to do some sort of data step preprocessing I suspect, but that's very hard to explain without more detail as to how the variables are named (ie, if there is a relationship between them).

Upvotes: 2

Related Questions