Create character macro from todays date

[wixol87]

Hi!

I use SAS 9.4.

I have a query that gives me a output with observations after a given date. This date is always two months ago. I want to create a macro that automaticly gives me all observations within the last two months.

The problem is that the variable that i run this query against, for example: "...and factdt>'2019-04-27' ", is a character-variable and not a date-variable.

I have tried to create a character-macro from the date here:

data test;
dato = sum(today()-60);
DATENEW=PUT(dato,yymmdd10.);
call symput('fra_dato', DATENEW);
run;

and have putted it into the query like this : and FactDt>'&fra_dato'

This gives me the following error-message:

ERROR: Open cursor error: ICommand::Execute failed. : Conversion failed when converting date
and/or time from character string.

What am I'm doing wrong?

[PeterClemmensen]

Welcome to the SAS Community 🙂

I can't see your data, so needless to say, this code is untested. However, you can do something like this

data want;
    set have;
    where input(factdt, yymmdd10.) ge intnx('month', today(), -2, 's');
run;

[wixol87]

Thank you! 🙂

The factdt is part of a SQL-query, here is the rest of the where-statement:

where b.custbranchorgid='xxx' and b.isrecent=1 and FactDt>'2019-04-27' and a.CrmOpportunityPhaseCd=100

Hope this is what you asked for?

I can't see how I can use your answer in this query

[PeterClemmensen]

I would think that this Where Clause gives you what you want

where b.custbranchorgid='xxx' and b.isrecent=1 and input(factdt, yymmdd10.) gt intnx('month', today(), -2, 's') and a.CrmOpportunityPhaseCd=100

[Tom]

---

@wixol87 wrote:

Thank you! 🙂

The factdt is part of a SQL-query, here is the rest of the where-statement:

where b.custbranchorgid='xxx' and b.isrecent=1 and FactDt>'2019-04-27' and a.CrmOpportunityPhaseCd=100

Hope this is what you asked for?

I can't see how I can use your answer in this query

---

The SAS macro processor does not modify text inside of single quotes.  

Is the WHERE clause part of the SAS code? 

FactDt>"&fra_dato"

Or are you using explicitly sending some SQL to a remote database that only allows sting literals to be enclosed in single quotes?

... from connection to ... 
( ... FactDt> %bquote('&fra_dato') ...
);

[Kurt_Bremser]

Maxim 28: Macro Variables Need no Formats.

Put the raw value into the macro variable:

data _null_;
call symputx('fra_dato',today()-60);
run;

then use it as is:

FactDt > &fra_dato

It's a given, of course, that dates should always be stored as such (numeric with a date format assigned). You only need 4 bytes instead of the 10 characters, and you can use all the SAS tools available for dates (functions, formats).

In your case this means use of the intnx() function, which is much better to calculate "2 months back" than the overly simplistic subtraction of 60 days.

If you need dashes in a displayed date, use the yymmddd10. format (note the third "d").

[Patrick]

@Kurt_Bremser 

If source variable factdt is really character as the OP states then I believe @PeterClemmensen's suggestion is what's going to work.

[wixol87]

Hi all!

Sorry for my late answer. I've been quite busy now before the summer vacation. Thanks for all your answers.

I found a solution that's working by using some code I found in a program, but i don't know why this is working and not my earlier tries

However, here is what I did:

data test;
dato = sum(today()-65);
DATECRM=PUT(dato,yymmdd10.);
run;

proc sql noprint;
select distinct "'"||strip(DATECRM)||"'" into :DATECRM separated by ' ' from test;
quit;
%put &DATECRM.;

proc sql;
connect to oledb as db_edw (provider=sqloledb properties=(
"Data Source" = "LHPM-SQL-PROD"
"Initial Catalog" = "LHPMMART"
"Integrated Security" = "SSPI"
"Persist Security Info" = "False") BCP=YES) ;
.

.

.

.

where b.custbranchorgid='xxxx' and b.isrecent=1 and factdt>&DATECRM. and a.CrmOpportunityPhaseCd=100
);
quit;

 I can't see who wrote it, but I have as you can see a connection.

[Kurt_Bremser]

This:

data test;
dato = sum(today()-65);
DATECRM=PUT(dato,yymmdd10.);
run;

 

proc sql noprint;
select distinct "'"||strip(DATECRM)||"'" into :DATECRM separated by ' ' from test;
quit;
%put &DATECRM.;

is equivalent to

data _null_;
call symputx('datecrm',cats("'",put(today()-65,yymmdd10.),"'"));
run;
%put &datecrm.;

The data _null_ is only there to make use of single quotes easier.

You have now moved the single quotes into the macro variable, so you do not need them where they would prevent macro variable resolution. Sometimes it is important to have quotes and formatted values in macro variables (exception to Maxim 28).

[Tom]

There are two reasons why you might need to use single quotes instead of double quotes around a value.

1. You are pushing the code into a remote database that does not recognize string in double quotes as string literals. (This seems to be your situation.)
2. You want to prevent the macro processor from evaluating macro triggers ( & and % ) in the string.

If you frequently need to add single quotes around macro variable values (or other macro code expressions) then I have found it useful to have an autocall macro for that.

https://raw.githubusercontent.com/sasutils/macros/master/squote.sas

%let DATECRM=%squote(%sysfunc(putn("&sysdate9"d-65,yymmdd10)));
...
 factdt>&datecrm