topic Re: Have a statement evaluated and the value be used as a macro parameter using %Let in SAS Programming

Have a statement evaluated and the value be used as a macro parameter using %Let

SmcGarrett — Mon, 06 Feb 2017 22:09:59 GMT

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,

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

LaurieF — Mon, 06 Feb 2017 22:18:12 GMT

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.

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

SmcGarrett — Mon, 06 Feb 2017 23:26:19 GMT

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.

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

Reeza — Mon, 06 Feb 2017 23:27:23 GMT

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.

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

LaurieF — Mon, 06 Feb 2017 23:32:43 GMT

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;

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

SmcGarrett — Mon, 06 Feb 2017 23:47:12 GMT

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

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

LaurieF — Tue, 07 Feb 2017 00:15:09 GMT

Sorry - my correction, or something else?

Re: Have a statement evaluated and the value be used as a macro parameter using %Let

LaurieF — Tue, 07 Feb 2017 00:47:33 GMT

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.