DATA Step, Macro, Functions and more

need help to create date type variable using macro variable in passthrough

Reply
Super Contributor
Posts: 271

need help to create date type variable using macro variable in passthrough

Hi Team, 

Advance thanks for your help. Need help. 

IN below code i want to create month variable as datetype but i am able to create it. i have tried to used input, i have tried " "d but failed. Please suggest
%MACRO APP();
%LET X="01JAN2017"D;
%DO I = 0 %TO 11;
%LET A1=%SYSFUNC(INTNX(MONTH,&X.,&i.,B),date11.);
%LET A2=%SYSFUNC(INTNX(MONTH,&X.,&i.,E),date11.);
%LET A1_=%SYSFUNC(INTNX(MONTH,&X.,&i.,E),ddmmyyd10.);
%LET A4=%SYSFUNC(INTNX(MONTH,&X.,&i.,B),DATE9.);
%LET A3=%SYSFUNC(SUBSTR(&A1_,4));
PROC SQL;
CONNECT TO ORACLE
(PATH= USER=
PASSWORD=
CREATE TABLE ADHOC6_E.APPLIED_&A4._(COMPRESS=YES) AS
SELECT * FROM CONNECTION TO ORACLE
(SELECT DISTINCT
%bquote('&A4.') as Month,
STUD_ID,
COUNT(CHARGE_CODE) AS CT_PENMC_APPLIED,
SUM(TOTAL_PRICE) AS PENMC_AMT_APPLIED
FROM table
WHERE PERIOD=%bquote('&A3.')
AND SCHM_TYPE='CAA' AND CHARGE_CODE in ('ABCD')
GROUP BY STUD_ID
ORDER BY STUD_ID
);
DISCONNECT FROM ORACLE;
QUIT;

 

%END;
%MEND;
%APP;

 

Regards .

Super User
Super User
Posts: 9,599

Re: need help to create date type variable using macro variable in passthrough

It has been mentioned time and time again.  Firstly please format code appropriately.  Use the {i} above the post window to post code as this retains formatting.  Avoid coding all in uppercase, use indents etc. to make code readable.  Secondly, macro is not a replacement for base SAS coding!  Macro language is a find and replace system, it takes some text, and places it in the code, or generates some text based on conditions.  From what I can tell from the mass of shouting is this, you want to take the previous 11 months data from a table.  You can do this very simply using base SAS - again, this is because this is the programming language, its built to do data processing and such like, with:

data want;
  set have;
  where intnx('month','02JAN2017'd,11) <= input(cats('01',period),date9.) <= '02JAN2017'd;
run;

Its not a good idea to split data up as you are doing in the code below, as then you would need to create macro code to loop over the data, and process it, whereas keeping the data together does not require this.  The counts and sums can be done quite simply using proc means, in fact you can use the where clause above in the proc means and simplfy the code even more.

So, start by writing simple Base SAS code.  Once that works and does what you want, then investigate which bits might benefit from being converted into macro, as in all cases Base SAS is simpler, easier to maintain and more robust, so adding macro will only degrade your code, and if there is no benefit don't do it.

Super Contributor
Posts: 271

Re: need help to create date type variable using macro variable in passthrough

Dear Thanks for your replay. I able solve my in normal Proc SQL using macro . But i am facing problem with using bacase there the problem of " ".

KIndly suggest me. I am getting variable value not getting any error. but value as a text i m getting. But i want that as a datetype.

 

Regards

 

Super User
Posts: 6,753

Re: need help to create date type variable using macro variable in passthrough

It's not clear how many problems you are encountering, beginning with the fact that there is no such thing as a date type in SAS.  So I'm going to give you a solution for one key issue.  At the same time, there may be other problems that are not yet addressed.

 

You have this type of code in several places:

 

%bquote('&A4.')

 

This will not allow &A4 to resolve because of the single quotes.  Switching to double quotes is a solution, if SQL permits that:

 

"&A4."

 

But if SQL doesn't permit that, you can instead use:

 

%unquote(%str(%'&A4.%'))

 

That will get you the resolved value of the macro variable, surrounded by single quotes.

Super Contributor
Posts: 271

Re: need help to create date type variable using macro variable in passthrough

Posted in reply to Astounding

Hi ,

 

I am having problem only in side of pass through with oracle connection. In normal proc sql i am not having problem.

in normal proc sql i can use simple "&a."d as month format date9. 

 

suggest me in side pass through with oracle.

 

Thanks and regards

 

Super User
Posts: 6,753

Re: need help to create date type variable using macro variable in passthrough

It's already in my post, but here is a recap of the key points.  You have this:

 

"&A4."d

 

Since Oracle won't accept double quotes, you need to replace that with:

 

%unquote(%str(%'&A4.%'d))

Super User
Super User
Posts: 8,073

Re: need help to create date type variable using macro variable in passthrough

Assuming that you have set the macro variables A3 and A4 to the proper values then perhaps the issue is the macro quoting introduced by the use %BQUOTE()?  You should try removing the quoting by wrapping the call in the %UNQUOTE() function.

%unquote(%bquote('&A4.'))

Or you could just make and use a simple macro for quoting values with single quotes. Like this one:   https://github.com/sasutils/macros/blob/master/squote.sas

 

%macro squote(value);
%sysfunc(quote(%superq(value),%str(%')))
%mend squote;

Then in your code you could write

%squote(&A4.)
Super Contributor
Posts: 271

Re: need help to create date type variable using macro variable in passthrough

HI Sir,

 

Thanks for your reply.

 

I think i was not able to discribe my question

 

suppose 

%let A=09DEC2017

now i want to create a date variable month in my date set .

in normal sql what i am using simply "&A."d as month format date9.

but at the same time when i using same code inside oracle passthrough its not working. to get the only vaule i can use single quote like '&A.' but i want it as datetype. how can i do that.

 

Please suugest

Super User
Posts: 6,753

Re: need help to create date type variable using macro variable in passthrough

%unquote(%str(%'&A.%'d))

Super Contributor
Posts: 271

Re: need help to create date type variable using macro variable in passthrough

Posted in reply to Astounding

getting error :

 

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT DISTINCT '01JAN2018'd as
Month, COUNT(CASE WHEN (CHARGE_CODE='AVBAL') THEN CHARGE_CODE END) AS AVBAL_CHG_CT_REC, COUNT(DISTINCT CASE WHEN
(CHARGE_CODE='AVBAL') THEN FORACID END) AS AVBAL_ACC_CT_REC, SUM(CASE WHEN (CHARGE_CODE='AVBAL') THEN APPROPRIATED END) AS
AVBAL_AMT_REC, COUNT(CASE WHEN (CHARGE_CODE='CCNNA') THEN CHARGE_CODE END) AS CCNNA_CHG_CT_REC, COUNT(DISTINCT CASE WHEN
(CHARGE_CODE='CCNNA') THEN FORACID END) AS CCNNA_ACC_CT_REC, SUM(CASE WHEN (CHARGE_CODE='CCNNA') THEN APPROPRIATED END) AS
CCNNA_AMT_REC, COUNT(CASE WHEN (CHARGE_CODE='CHQRT') THEN CHARGE_CODE END) AS CHQRT_CHG_CT_REC, COUNT(DISTINCT CASE WHEN
(CHARGE_CODE='CHQRT') THEN FORACID END) AS CHQRT_ACC_CT_REC, SUM(CASE WHEN (CHARGE_CODE='CHQRT') THEN APPROPRIATED END) AS
CHQRT_AMT_REC, COUNT(CASE WHEN (CHARGE_CODE NOT IN ('AVBAL','CCNNA','CHQRT')) THEN CHARGE_CODE END) AS OTHERS_CHG_CT_REC,
COUNT(DISTINCT CASE WHEN (CHARGE_CODE NOT IN ('AVBAL','CCNNA','CHQRT')) THEN FORACID END) AS OTHERS_ACC_CT_REC, SUM(CASE
WHEN (CHARGE_CODE NOT IN('AVBAL','CCNNA','CHQRT')) THEN APPROPRIATED END) AS OTHERS_AMT_REC FROM
table_name WHERE SCHM_TYPE='CAA' AND (RECOVER_DATE BETWEEN '08-2012' AND '08-2012').

Super User
Posts: 6,753

Re: need help to create date type variable using macro variable in passthrough

Sorry, I don't think I can help here.  I know a lot about macro language, so your original question was something I could help with.  But I know much less about SQL, particularly if pass-through is involved.  Good luck.

Super User
Super User
Posts: 8,073

Re: need help to create date type variable using macro variable in passthrough


Aman4SAS wrote:

HI Sir,

 

Thanks for your reply.

 

I think i was not able to discribe my question

 

suppose 

%let A=09DEC2017

now i want to create a date variable month in my date set .

in normal sql what i am using simply "&A."d as month format date9.

but at the same time when i using same code inside oracle passthrough its not working. to get the only vaule i can use single quote like '&A.' but i want it as datetype. how can i do that.

 

Please suugest


That is a question for ORACLE.  It will not understand the date literal syntax that SAS uses.  You need to use a method that Oracle understands since you are sending the code directly to the database.  You might try '09-DEC-2017' which is a format you can generate with SAS using the DATE11. format.  Or look into the TO_DATE() function in Oracle that will let you tell it how to convert the string literal to a date.

Ask a Question
Discussion stats
  • 11 replies
  • 173 views
  • 0 likes
  • 4 in conversation