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-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

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

 

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.

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
  • 7 replies
  • 804 views
  • 1 like
  • 3 in conversation