SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SmcGarrett
Obsidian | Level 7

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, 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LaurieF
Barite | Level 11

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;

View solution in original post

7 REPLIES 7
LaurieF
Barite | Level 11

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.

SmcGarrett
Obsidian | Level 7

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.

Reeza
Super User

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.

LaurieF
Barite | Level 11

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;
SmcGarrett
Obsidian | Level 7

It worked! It was not an issue with the where statement or it being a proc import or database. Just the formating. Thanks! 

 

 

LaurieF
Barite | Level 11

Sorry - my correction, or something else?

LaurieF
Barite | Level 11

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1272 views
  • 1 like
  • 3 in conversation