CODEWITHSUNDEEP
sqlsas
J Ma
J Ma

Reputation: 11

Using "for loop" in SAS SQL?

I am using SQL in SAS. I have lots of similar variable names in my data, such as "var1, var2, ..., var100". I know it could be written like 

proc sql; select xx from xxx where var1 = a or var2 =a or var3 =a;run;

I wonder if SQL has a function like "for loop" to make this task easier?

Thank you for your help!

Upvotes: 1

Views: 2267

Answers (6)

Kiran
Kiran

Reputation: 3315

You can macrovariable and then use it. But it is long tedious process and datastep is much simpler. You can do this in proc sql as shown below.

/*first create dataset to test*/
data have;
  input var1 var2 var3 var4 newvar  a;
  datalines;
 10 20 30 40 60 10
10 20 30 40 60 90
10 20 30 40 60 20
;
run;

 /* create macro variable*/
 proc sql;
   select catt(name, "  =  ", " a  or ") into :VAR SEPARATED by " "  
 from  dictionary.columns 
 where memname = 'HAVE'
 and prxmatch("m/^VAR\d+$/i", trim(name)) > 0;/* to find variable var with  digit*/

%put &VAR;

It gives var1 = a or var2 = a or var3 = a or var4 = a or

/* do a substring to remove last or */
%let final_var = %substr(&var, 1, %length(&var)-2);

%put &final_var;

It gives var1 = a or var2 = a or var3 = a or var4 = a

 /*Now you can use the macro variable in your query, will give you desired 
result*/

proc sql;
select * from have
where &final_var;

Upvotes: 0

Reeza
Reeza

Reputation: 21264

As Tom indicated, this would be significantly easier in a data step:

data want;
   set xxx;
where whichc('a', of var1-var1000);
run;

Upvotes: 2

Tom
Tom

Reputation: 51566

You can use the WHICH() or WHICHC() function to make that type of test. It will return the index of the first variable that matches and zero if none match. SAS will treat 0 as false.

where which(a,var1,var2,var3)

Unfortunately if you use PROC SQL you must list each variable name. Unlike DATA step it does not support variable lists.

Upvotes: 0

Alessandro Mento
Alessandro Mento

Reputation: 21

You could use macros to do the work. For example:

%macro loopQuery;

  proc sql; 
    select xx 
    from xxx 
    where var1 = a 
     %do i=2 %to 100;
       or var&i.=a
     %end;
    ;
  run; 
%mend;

%loopQuery;

Upvotes: 0

ccarpenter32
ccarpenter32

Reputation: 1077

There's a Do loop functionality in every interation of SAS as far as I know.

IIRC, I think it works something like :

%do i=1 %to [##];
     ... do iterative stuff ...
%end;

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Depending on the database variant, you might be able do this:

... WHERE a IN (var1, var2, var3, etc);

but that will be the best you can do, and I don't know if SAS supports it.

Upvotes: 0

Related Questions