BookmarkSubscribeRSS Feed
Wouter
Obsidian | Level 7
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.
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Check the DO statement syntax - you only code I= once, not twice.

Scott Barry
SBBWorks, Inc.
LAP
Quartz | Level 8 LAP
Quartz | Level 8
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"));
Ksharp
Super User
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
Wouter
Obsidian | Level 7
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!!
Peter_C
Rhodochrosite | Level 12
> 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 801 views
  • 0 likes
  • 5 in conversation