Passing sas variable to sql pass through statement

Question

Using SAS I pull data from an SQL database using a pass for speed, since the databases are large enough. The following code works as expected.

%let expectdate1 = '2013-07-03';*/ proc sql; connect to ***** as abc (tdpid=***** user='****' password='*****' ); create table Searched_data as select * from connection to dss( SELECT * FROM database.tablename WHERE CAPTURE_DT >= '2013-07-01' and CAPTURE_DT <= &expectdate1 ); disconnect from abc; quit; 

Problems arise when I want them to expect 1.

so i replace

 %let expectdate1 = '2013-07-03';*/ 

from

 %let expectdate1 = put(Date(),YYMMDD10.); 

This does not work, and the Im get error is something like

 ....WHERE CAPTURE_DT >= '2013-07-01' and CAPTURE_DT <= put(Date(),YYMMDD10) ..... 

This way, it does not evaluate my date code, and instead passes the actual SQL code, not the resulting string.

Answer 1

Shorack is correct that the PUT statement cannot be used with% SYSFUNC, however you can successfully use PUTN.

You just need the following.

 %LET EXPECTDATE1 = %SYSFUNC(PUTN(%SYSFUNC(DATE()),YYMMDD10.)); %put EXPECTDATE1=&EXPECTDATE1.; 

SASLOG:

 EXPECTDATE1=2013-08-05 

Answer 2

Note: Edited for the single quotes you need.

Let me first provide a solution that works, and then explain why your approach does not work. Use this piece of code instead:

 data _NULL_; call symput("expectdate1",cats("'",put(Date(),YYMMDD10.)),"'"); run; 

The above code snippet will create your string and then put it in the macro variable expectdate1.

So why did your code not work?
This is because you do not distinguish between SAS functions and SAS macros. put (Date (), YYMMDD10.) are not macro functions (easily distinguishable because they start with a percent sign. →% <-)

So, SAS Macro does not evaluate it and just puts part of the code in your SQL statement, literally.

Now there is something called the% sysfunc function. This is a macro function that will perform a closed normal function. Thus,% sysfunc (Date ()) will be resolved by the SAS macro before setting the macroenddate variable. Note that each function must be enabled by the% sysfunc function, i.e.

 %let someVariable = %sysfunc(mean(max(1,3),5)); /*WRONG*/ %let someVariable = %sysfunc(mean(%sysfunc(max(1,3)),5)); /*RIGHT*/ 

This suggests that this does not work for some SAS functions, and this is one of them. That's why I presented the solution from above: use the data step to prepare it the way you like and write the result in a macro variable.