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,
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;
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.
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.
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.
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;
It worked! It was not an issue with the where statement or it being a proc import or database. Just the formating. Thanks!
Sorry - my correction, or something else?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.