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
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);
But since you say your original data is very large I would probably recommend using the between-and operator on the where = clause.
(keep=period bron loannr ENR where=(datepart(Period) between mdy(07,01,2010) and mdy(12,01,2010) and bron ne "FAF"));
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).
> 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
> Thanks for your answers!!
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