topic Re: Filtre on date in SAS Programming

Filtre on date

SASdevAnneMarie — Mon, 08 Feb 2021 14:57:50 GMT

Hello Experts,

I can't run this code, it takes a long time. I'm wondering if my code is right. Thank you.

DATA TBT; 
set TBM; 
     (where=((D_VALO>='31DEC2019:00:00:00'dt)
                and (D_VALO <='31DEC2020:00:00:00'dt)));
run;

Re: Filtre on date

PaigeMiller — Mon, 08 Feb 2021 15:09:40 GMT

Can you show us a portion of the data set TBM, showing the variable D_VALO for a few records?

When you say it is taking too long, how big is the data set?

What happens if you change the code to this:

DATA TBT; 
set TBM /* Note I removed a semicolon here */
     (where=((D_VALO>='31DEC2019:00:00:00'dt)
                and (D_VALO <='31DEC2020:00:00:00'dt)));
run;

Re: Filtre on date

ballardw — Mon, 08 Feb 2021 15:11:24 GMT

Try picking one of these:

DATA TBT; /* where as a DATASET OPTION*/ set TBM (where=((D_VALO>='31DEC2019:00:00:00'dt) and (D_VALO <='31DEC2020:00:00:00'dt))); run;

DATA TBT; set TBM; /* Where statement*/ where (D_VALO>='31DEC2019:00:00:00'dt) and (D_VALO <='31DEC2020:00:00:00'dt); run;

It shouldn't make much difference in run time by you can use a single comparison like

'31DEC2019:00:00:00'dt <=  D_VALO  <= '31DEC2020:00:00:00'dt

Which is at least a little easier to type and see the logic involved.

How many records are in the TBM data set and how long is "too long"?

Re: Filtre on date

LinusH — Mon, 08 Feb 2021 15:12:12 GMT

We need much more info tu understand/help.

Were is your data located?

Looks like in your saswork, but is that on your local computer or remote (what specs does your PC/server have).

How large is the source data set?

Please provide a log with options FULLSTIMER and MSGLEVEL=I set.

Re: Filtre on date

maguiremq — Mon, 08 Feb 2021 15:15:23 GMT

One issue is your semi-colon placement on the SET statement. Your WHERE condition should be applied to the SET statement.

DATA TBT; 
set TBM (where=((D_VALO>='31DEC2019:00:00:00'dt)
                and (D_VALO <='31DEC2020:00:00:00'dt)));
run;

Alternatively, you could do this:

DATA TBT; 
set TBM; 
where D_VALO>='31DEC2019:00:00:00'dt and D_VALO <='31DEC2020:00:00:00'dt;
run;

I don't really work with date literals like this, let alone date-time literals. Nor can I know if this works without some example data. If possible, post some reproducible code or data in a DATALINES statement.

Re: Filtre on date

SASdevAnneMarie — Mon, 08 Feb 2021 17:24:46 GMT

Thank you, the problem is that my data is very huge.
I'm wondering if there is another way to write the data step. When I'm using this code my programm is running the hours 😞 Thank you !