topic Re: Loop inside Proc SQL using macro variables in where clause in SAS Programming

Loop inside Proc SQL using macro variables in where clause

pradeep_kadasi — Sat, 17 Apr 2021 23:25:57 GMT

I have a dataset with 3 Columns and hundreds of observations as in the snapshot. Using these values, I have to pull data from a Database with the below where clause -

Account = 'ACCOUNT_NBR'
and Alert_Date >= START_DT
and Alert_Date <= END_DT

For each account number in the dataset the Start and End dates are different and I want to loop it using macro variables, I could manage one but its generating 1 table per 1 where clause. I want to create a single table which satisfies the above where clause with macros.

Re: Loop inside Proc SQL using macro variables in where clause

PaigeMiller — Sun, 18 Apr 2021 00:40:18 GMT

Please show us the full code that you tried. What is Alert_Date, it is not in your data set.

Where would the loop fit in? Explain that part? SQL ought to be able to select observations that meet a condition without a loop.

Re: Loop inside Proc SQL using macro variables in where clause

pradeep_kadasi — Sun, 18 Apr 2021 01:45:23 GMT

Alert_Date is in the Database from which I intend to pull data with a where clause which is not static. Below is how I want -

1st Iteration-

Account = X1
and Alert_Date >= 14MAR2021
and Alert_Date <= 13APR2021

2nd Iteration-

Account = X2
and Alert_Date >= 20FEB2021
and Alert_Date <= 22MAR2021

and so on...all the values are in the data set I have using which I need to pull data from a DB.

Below is the code I tried (includes multiple blocks) -

*This code is for getting count of records, add index to each record.

Data Temp3;
Set mylib.Alerts_Distinct END=Last;
By DECISION;
Retain Counter;

If (First.DECISION) then Do;
Counter=1;
End;
Else Do;
Counter+1;
End;
Run;

*This code is for creating the macro variables for Account number, Start and End dates

Options Symbolgen;

DATA Temp4;
SET Temp3 END=LAST;

If LAST then
Call SYMPUT('Count', PUT(Counter, 10.));
RUN;

Data Temp5;
Set Temp4;
call symputx('ACCOUNT_NUMBER'||Left(put(_n_,5.)), CURRENT_ACCOUNT_NBR);
call symputx('START_DATE'||Left(put(_n_,5.)), LOSS_START_DT);
call symputx('END_DATE'||Left(put(_n_,5.)), LOSS_DT);
run;

The above code generates

ACCOUNT_NUMBER1, ACCOUNT_NUMBER2 etc. holding values of Account numbers

START_DATE1, START_DATE2 etc. holding the values of start dates of each records

END_DATE1, END_DATE2 etc. holding the values of end dates of each records

%macro sqlloop(start,end);

Proc Sql;
%DO i=&start. %TO &end.;
create table Alerts as
select * from Database
where
CONTACT_VALUE = '&ACCOUNT_NUMBER&i'
and Alert_Date >= %Format('&START_DATE&i',Date9.)
and Alert_Date <= %Format('&END_DATE&i',Date9.)
;
%END;
Quit;

%Mend;

%sqlloop(start=1, end=&Count.);

Re: Loop inside Proc SQL using macro variables in where clause

Patrick — Sun, 18 Apr 2021 05:47:05 GMT

@pradeep_kadasi

The way how this is done normally:

1. you upload your small table into the database (could be a temporary table)

2. you inner join your small table in the database with the big table in the database

3. you download the result set (that's just happening via the create table statement in the SAS SQL portion).

Here a whitepaper describing the approach for joining a SAS with a Teradata table. It's in principle the same for any DB.

You could of course define a SAS macro which generates code like....

...
from db_table
where account_id='a' and from_date=.. and to_date=...
or  account_id='b' and from_date=.. and to_date=...
or  account_id='c' and from_date=.. and to_date=...
.....

...but this would result in a rather inefficient query plus at least some DB's have also an upper limit for how much code a single SQL can contain (it used to be 32KB for Oracle; may be different for recent versions).

I don't recommend to use this approach but below just to demonstrate how generation of a where clause could work. I'm using a data step and not a macro to generate the code as this allows me to print the generated code in a way that's easy to read and without executing it.

data have;  
  infile datalines truncover dsd dlm=' ';
  input account $ (start_dt end_dt) (:$9.);
datalines;
a 01jan2019 15jan2020
b 10feb2020 15feb2020
c 15mar2020 10mar2021
;

filename codegen temp;
data _null_;
/*  file codegen;*/
  file print;
  set have end=last;
  if _n_=1 then
    do;
      put 
        'proc sql;' /
        '  create table want as ' /
        '    select * ' /
        '    from db.table ' /
        '    where ' 
        ;
    end;
    put
      "      "
      "account='" account +(-1) "' and "
      "start_dt='" start_dt +(-1) "'d and "
      "end_dt='" end_dt +(-1) "'d"
      @ ;
    if not last then put " or";
    else put;
    if last then
      do;
        put
          '    ;' /
          'quit;'
          ;
      end;
run;

data _null_;
  file codegen mod;
  stop;
run;
%include codegen /source2;
filename codegen clear;

...or even simpler but also not really suitable for hundreds of where conditions....


proc sql noprint;
  select 
    cats("account='",account,"'")
    ||' and '||
    cats("start_dt='",start_dt,"d'")
    ||' and '||
    cats("end_dt='",end_dt,"d'")
    into :where_cond separated by ' or '
  from have
  ;
quit;
%put %nrbquote(&where_cond);

proc sql noprint; 
  create table want as  
    select *  
    from db.table  
    where  
      %nrbquote(&where_cond)
    ;
quit;

Re: Loop inside Proc SQL using macro variables in where clause

Kurt_Bremser — Sun, 18 Apr 2021 09:06:49 GMT

One join is all that's needed:

proc sql;
create table want as
  select b.*
  from account a left join mylib.Alerts_Distinct b
  on a.account_nbr = b.account and b.alert_date between a.start_dt and a.end_dt
;
quit;

Re: Loop inside Proc SQL using macro variables in where clause

Patrick — Sun, 18 Apr 2021 09:07:51 GMT

@Kurt_Bremser Isn't that going to download the full big DB table into SAS prior to joining?

Re: Loop inside Proc SQL using macro variables in where clause

Kurt_Bremser — Sun, 18 Apr 2021 09:11:34 GMT

If that is a concern, the account table should be uploaded to the DB and the query run in a pass-through.

I mainly wanted to show that no macro loop is needed.

Re: Loop inside Proc SQL using macro variables in where clause

Tom — Sun, 18 Apr 2021 15:51:05 GMT

You have design problems with your macro.

The loop is overwriting the target dataset so only the results of the last one will exist.
To use a macro variable, like I, as a suffix on macro variable name you need to double the initial & so it will re-process the resulting name with the suffix added.
Macro code inside strings bounded by single quotes is ignored. So either use double quotes. Or just generate the macro variables in the format needed and don't add in extra quotes.

For example you could create separate datasets.

%macro sqlloop(start,end);
%local i;
proc sql;
%do i=&start. %TO &end.;
create table Alerts&i as
select  * from mylib.mydataset
where CONTACT_VALUE = "&&ACCOUNT_NUMBER&i"
  and Alert_Date between &&start_date&i and &&end_date&i
;
%end;
quit;
%mend;

Also the code that is generating the series of macro variables could be a lot simpler

data _null_;
  set temp3 end=last;
  call symputx(cats('account_number',_n_), current_account_nbr);
  call symputx(cats('start_date',_n_), loss_start_dt);
  call symputx(cats('end_date',_n_), loss_dt);
  if last then call symputx('count',_n_);
run;

If you are going to use pass thru SQL (unlike the example code you showed) then format the dates in the style that the SQL of that remote database needs. For example many want dates to look like '2021-04-18'.

...
  call symputx(cats('start_date',_n_), quote(put(loss_start_dt,yymmdd10.),"'"));
  call symputx(cats('end_date',_n_), quote(put(loss_dt,yymmdd10.),"'"));
...

%macro sqlloop(start,end);
%local i;
proc sql;
connect using mylib;
%do i=&start. %TO &end.;
create table Alerts&i as
select  * from connection to mylib
(select * from mydataset
where CONTACT_VALUE = "&&ACCOUNT_NUMBER&i"
  and Alert_Date between &&start_date&i and &&end_date&i
)
;
%end;
quit;
%mend;