topic Re: Need help with data filter in SAS Programming

Need help with data filter

pikardenz — Fri, 05 May 2023 18:22:12 GMT

Hey everyone, I could use some help with a query. Im using sas studio but without much knowledge from sql. I can read and understand what it does, thats about it.

My query contains several filters with the same date, so in my previous program sas eg I used a prompt to set this date once, and it would be used throughout the entire flow. If i wanted to change this date I could just adjust this value instead of going through the set finding all the date filters.

Now someone told me I can do about the same in sas studio, by creating a program that says:

%global DateFrom %let DateFrom = ‘01nov2022’

And in the queries I could just filter on the name Datefrom.

I also tried &Datefrom because this is what I used in sas eg.

This isn’t working at the moment, could someone guide me in the right direction. Preferably by using the dropdown menu’s as filter, not by using code.

Thanks in advance!

Re: Need help with data filter

PaigeMiller — Fri, 05 May 2023 18:35:39 GMT

Note the syntax corrections here and the straight quotes rather than curly quotes:

%global DateFrom;
%let DateFrom = '01nov2022'd;

But of course, it would really help to see where and how you intend to use the DATEFROM macro variable. Please show us.

Re: Need help with data filter

Tom — Fri, 05 May 2023 18:37:13 GMT

You did not include the semicolons to end your statements. So your code should look like:

%global DateFrom;
%let DateFrom = '01nov2022' ;

You don't really need the first one since you are NOT running the code inside a macro.

If you want to use &DATEFROM as an actual DATE value then you should probably set the value to a date literal instead of character literal.

%let DateFrom = '01nov2022'd ;

Let's make some sample data we can try it out on.

data example;
  do week=0 to 10;
    date=intnx('week','01OCT2022'd,week,'same');
    output;
  end;
 format date date9.;
run;

Now we can use the macro variable in a WHERE statement (that is an easy way to "filter" in SAS code).

proc print data=example;
  where date > &datefrom;
run;

We can do the same thing using the filter open when browsing a dataset in SAS/Studio

Re: Need help with data filter

mkeintz — Fri, 05 May 2023 19:20:48 GMT

I would discourage using

 %let DateFrom = ‘01nov2022’;

for macrovar datefrom. Instead consider dropping the quotes, as in

 %let DateFrom = 01nov2022 ;

Then you can use a filter such as

where date >= "&datefrom"d

Date literals like '01nov2022'd and "01nov2022"d have identical underlying date values. But using single quotes with a macrovariable inside the quotes (as in '&datefrom'd) would prevent the macro processor from resolving &datefrom to 01nov2022, while double quotes (as in "&datefrom"d) will support resolution of the macrovar DATEFROM.