CODEWITHSUNDEEP
sqlsaspass-through
mathemagician
mathemagician

Reputation: 183

How would I include a where statement within SQL pass through code in SAS?

I am basically trying to combine the two code blocks below by including a where statement within the sql block, but it gives me an error. How would I be able to do it?

%let yyyy_mm_dd = 2021-09-30;
proc sql;
      connect to odbc(noprompt="driver=&driver;database=&database ;Server=&server;Trusted_Authentication=yes"
           user=&user password="&pass!");
      create table snapshot.snapshot_&year_quarter as
           select * from connection to odbc
           (select *
                 from RRS.FactActiveLoanRiskRatingSnapShotData);
      disconnect from odbc;
quit;

data snapshot.snapshot_&year_quarter;
      set snapshot.snapshot_&year_quarter;
      where CoreAsofDate = "&yyyy_mm_dd";
run;

Upvotes: 1

Views: 653

Answers (1)

Joe
Joe

Reputation: 63424

You can just add it directly after the close parenthesis.

%let yyyy_mm_dd = 2021-09-30;
proc sql;
      connect to odbc(noprompt="driver=&driver;database=&database ;Server=&server;Trusted_Authentication=yes"
           user=&user password="&pass!");
      create table snapshot.snapshot_&year_quarter as
           select * from connection to odbc
           (select *
                 from RRS.FactActiveLoanRiskRatingSnapShotData)
           where CoreAsOfDate = "&yyyy_mm_dd";
      disconnect from odbc;
quit;

But even better would be to include it in the pass through, so you don't have as much data passed to you. You'd have to format it slightly differently possibly - unless it's a character variable - as it would have to be a legal statement in the database's language, but you can use a macro variable (That's parsed by SAS before it sends it down). You just have to shoehorn the single quotes inside the macro variable (assuming it's standard SQL and double quotes aren't allowed). For example:

%let yyyy_mm_dd = '2021-09-30';
proc sql;
      connect to odbc(noprompt="driver=&driver;database=&database ;Server=&server;Trusted_Authentication=yes"
           user=&user password="&pass!");
      create table snapshot.snapshot_&year_quarter as
           select * from connection to odbc
           (select *
                 from RRS.FactActiveLoanRiskRatingSnapShotData
             where CoreAsOfDate = &yyyy_mm_dd.);
      disconnect from odbc;
quit;

If the variable is actually a date variable that SAS is converting to a string, you may need to use a different syntax to properly filter on a date variable.

Upvotes: 2

Related Questions