- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I have what I believe is a relatively simple task.
I have a program where a date variable is used as a macro parameter...something like this:
%program(data_file, date_start)
I want to add a let statement within the program which will essentially add days to the date variable to increase the amount of data that is processed.
I'd like it to look something like:
%program(data_file, date_start,numberofdays)
And it will process a data step that looks something like:
%let Date_end = &Date + &numberofdays;
proc import datafile="Datafile"
dbms=xlsx
out=work.Data;
sheet= "sheet1";
where Datetime >="&Date_start:6:00"DT and Datetime <= "&Date_end:5:59"DT;
run;
How do I make the %let statement evaluate &Date + &numberofdays????
Thanks,
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I didn't know you could put a where clause in proc import. Cool! So the date value needs to be converted back into an external representation again. Sorry about that.
%let date = 15Oct1983;
%let numberofdays = 15;
%let date_end = %sysfunc(putn(%sysevalf("&date"d + &numberofdays), date9.));
%put &date_end;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
For a start, you'll have to convert the date into "number of days since 1 Jan 1960".
There are a myriad of ways of doing this. What I'd recommend woud be to do it like this:
%let date = 15Oct1983;
%let numberofdays = 15;
%let date_end = %sysevalf("&date"d + &numberofdays);
%put %sysfunc(putn(&date_end, date9.));
The macro function %sysevalf, although it is primarily used for floating point evaluations, has a nice side-effect of allowing interpretation of external date values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,
It seems to want to work, but it appears to be having a problem converting the numeric into a date.
This is my error:
ERROR: CLI prepare error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to datetime.
SQL statement: execute InsightGetGoogleAnalytcisInfo "23JAN2017","20851".
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I'm not sure you can do a WHERE filter on PROC IMPORT the way you're invisioning.
Your initial code showed importing from an Excel file and the current error indicates a database access so there's a disconnect happening somewhere here.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I didn't know you could put a where clause in proc import. Cool! So the date value needs to be converted back into an external representation again. Sorry about that.
%let date = 15Oct1983;
%let numberofdays = 15;
%let date_end = %sysfunc(putn(%sysevalf("&date"d + &numberofdays), date9.));
%put &date_end;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It worked! It was not an issue with the where statement or it being a proc import or database. Just the formating. Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry - my correction, or something else?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The clue was SQL statement: execute InsightGetGoogleAnalytcisInfo "23JAN2017","20851"
20851 is clearly a SAS date value, but the first parameter is a date I'd expect ODBC (or whatever the engine is) to accept.