Help using Base SAS procedures

Extract data based on date --> should be very simple...!

Reply
Contributor
Posts: 62

Extract data based on date --> should be very simple...!

Hi all,

I've got a question, that may sound stupid, but no matter what I try, I can't get it work... Maybe I need some more coffee or so...?

I want to extract from a very large table some info, based on a begin date and an end date. The code I have right now looks like this:

%let date_from=mdy(07,01,2010);
%let date_to=mdy(12,01,2010);

data test;
Do i=&date_from to i=&date_to;
set x.loan(keep= period bron loannr ENR where=(period = dhms(i,0,0,0) and bron NE 'FAF'));
output;
end;
run;

ERROR: VARIABLE I IS NOT ON FILE X.LOAN.
Super Contributor
Super Contributor
Posts: 3,174

Re: Extract data based on date --> should be very simple...!

Check the DO statement syntax - you only code I= once, not twice.

Scott Barry
SBBWorks, Inc.
Frequent Contributor
Frequent Contributor
Posts: 76

Re: Extract data based on date --> should be very simple...!

I'm assuming that all you wish to do is subset your original data set based on a range of dates.

The problem (error message) here is that your original data set x.loan does not have a variable i on the data set. The syntax for the data set options that you use on the set statement applies specifically to x.loan. The variable i is actually created in the output data set test.

Two easy solutions come to mind...

You could try changing the where = (period=dhms(i,0,0,0) portion to an if statement in the data step

data test;
set x.loan(keep= period bron loannr ENR where=(bron NE 'FAF'));
do i = &date_from to &date_to;
if period = dhms(i,0,0,0);
output;
end;
run;

But since you say your original data is very large I would probably recommend using the between-and operator on the where = clause.

ie

(keep=period bron loannr ENR where=(datepart(Period) between mdy(07,01,2010) and mdy(12,01,2010) and bron ne "FAF"));
Super User
Posts: 9,682

Re: Extract data based on date --> should be very simple...!

Hi.
In addition to what LAP said, there is another problem in your code.
[pre]
%let date_from=%sysfunc(mdy(07,01,2010));
[/pre]



Ksharp
Contributor
Posts: 62

Re: Extract data based on date --> should be very simple...!

Hi,

Thank you all for your respons(es)!!

I solved this question by not capping it at the date_to --> in almost every case, date_to will be the current reporting date.

Since the extremely large file only exists of data with month-begin dates (so only the 1st of every month), a loop without a +1 month function took a severe time. I solved the question now by "> &date_from".

Concerning the other problem (with doens't apply anymore now): I needed the dhms function to transform datetime into date (could also divide by 84600 or so).

Thanks for your answers!!
Valued Guide
Posts: 2,175

Re: Extract data based on date --> should be very simple...!

> Hi,
>
> Thank you all for your respons(es)!!
>
> I solved this question

> anymore now): I needed the dhms function to transform
> datetime into date (could also divide by 84600 or
> so).
>
> Thanks for your answers!!

R_Win

when you use functions like dhms() in a where clause, make sure it is really neccessary, because SAS might be unable to pass the function to the platform holding that data and so bring back all the data to the sas session for filtering - a bit like using IF instead of WHERE.
Converting the date constants to datetime constants is not like the problem you might find converting data base values.
where rdbms.date_column between "&sasdate1:0:0:0"dt and "&sasdate2:0:0:0"dt
probably works as well as, if not better than
where rdbms.date_column between dhms("&sasdate1"d,0,0,0) and dhms("&sasdate2",0,0,0)
or this better version (where datetime conversion is done on constants in the macro language layer)
where rdbms.date_column between %sysfunc(dhms("&sasdate1"d,0,0,0)) and %sysfunc( dhms("&sasdate2"d,0,0,0))

and you might not be able to run
where (rdbms.date_column/84600) between "&sasdate1"d and "&sasdate2"d
as some rdbms do not allow normal arithmetic operations like divide, on dates


glad the problem was solved without the dhms() loop

peterC
Ask a Question
Discussion stats
  • 5 replies
  • 143 views
  • 0 likes
  • 5 in conversation