topic Re: proc sql where statement in SAS Enterprise Guide

proc sql where statement

alepage — Tue, 23 Feb 2021 16:13:45 GMT

Good morning,

I found a script on our server and I am not sure how to interpret it.

Here's a simplify version of the code. So we are trying to create a table named mytable from the contains of table10 which contains few variables , including the variable from and to. The variable from to contains date like 20081231 to 99991231.

So suppose that we have %let dateproduction='20200131'

I believe the le statement means less or equal but how will work the where statement?

Thanks for your help

proc sql;
create table mytable as
select *
from table10
where( calculated from le "&DATEPRODUCTION." le calculated to
);
run;

Re: proc sql where statement

PaigeMiller — Tue, 23 Feb 2021 16:20:17 GMT

Do you mean you want to extract all records where &dateproduction is greater than or equal to variable FROM and less than or equal to variable TO?

Re: proc sql where statement

Tom — Tue, 23 Feb 2021 16:33:37 GMT

Are FROM and TO character strings like '20081231' or numbers like 20,081,231?

If they are strings then generated SAS code in the WHERE clause needs to contain with single or double quotes, but your example code is going generate both double and single quotes. It they are numbers then you do not want any quotes.

The LE operator means less than or equal to, which you also write as <= .

You do not need the CALCULATED keyword in this example if FROM is a variable that is coming from the input TABLE10. If FROM was the name of some generated variable listed in the SELECT statement then you would need the CALCULATED keyword.

If FROM and TO are character you could keep the quotes in the macro variable:

%let dateproduction='20200131';
proc sql;
create table mytable as
  select *
  from table10
  where  (from le &DATEPRODUCTION. le to)
;
quit;

Or not put the quotes in the macro variable and instead add them back into the generated code.

%let dateproduction=20200131;
proc sql;
create table mytable as
  select *
  from table10
  where  (from le "&DATEPRODUCTION." le to)
;
quit;