DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

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

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
Solution
‎02-06-2017 07:43 PM
Super Contributor
Posts: 252

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

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


All Replies
Super Contributor
Posts: 252

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

[ Edited ]
Posted in reply to SmcGarrett

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.

Contributor
Posts: 34

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

[ Edited ]

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.

Super User
Posts: 19,850

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

Posted in reply to SmcGarrett

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.

Solution
‎02-06-2017 07:43 PM
Super Contributor
Posts: 252

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

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;
Contributor
Posts: 34

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

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

 

 

Super Contributor
Posts: 252

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

Posted in reply to SmcGarrett

Sorry - my correction, or something else?

Super Contributor
Posts: 252

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

[ Edited ]
Posted in reply to SmcGarrett

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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