topic Re: SAS Proc sql pass through (postgressql) - macro date in SAS Programming

SAS Proc sql pass through (postgressql) - macro date

rimonr — Mon, 30 Nov 2015 02:55:31 GMT

Hi there

I am trying to run the below and I get nothing:

%macro test2(ot=, SD=, ED=);
proc sql;
connect to postgres as x1(SERVER='XXXX.lan' DATABASE='XXXX' PORT='5432' USER='XXXX' PASSWORD='XXXX');
create table &ot. as
select * from connection to x1

(SELECT
distinct date_trunc('MONTH', syndicate_share_timestamp) as date,

(sum(account_transaction.transaction_base_amount)) as sum

FROM syndicate_ticket
inner join draw on (syndicate_ticket.draw_id=draw.draw_id)
inner join lottery on (draw.lottery_id=lottery.lottery_id)
inner join syndicate_share on (syndicate_ticket.syndicate_session_id = syndicate_share.syndicate_session_id)
inner join account_transaction ON (syndicate_share.account_transaction_id=account_transaction.account_transaction_id)

WHERE
syndicate_share.syndicate_share_timestamp > %BQUOTE('&SD')
and syndicate_share.syndicate_share_timestamp < %BQUOTE('&ED')

and syndicate_share_status_id = 40
group by date
;
quit;

%mend test2;
%test2 (ot=work.a1, SD='2015-10-01', ED='2015-10-15');

%test2 (ot=work.a2, SD='2015-10-16', ED='2015-10-31);

I have also tried by %STR(%'&SD%') and %STR(%'&ED%') and also simply by &SD and &ED.

Result: nothing happens.

Please help..

Re: SAS Proc sql pass through (postgressql) - macro date

kannand — Mon, 30 Nov 2015 03:56:22 GMT

Would it be possible to share the log ?

Re: SAS Proc sql pass through (postgressql) - macro date

kannand — Mon, 30 Nov 2015 04:01:07 GMT

Also, have you tried running the query against individual tables? and isolate which condition is causing an empty result ? I'd split this into smaller pieces and run them expanding one after another to pinpoint the root cause....

Re: SAS Proc sql pass through (postgressql) - macro date

rimonr — Mon, 30 Nov 2015 04:40:27 GMT

Hi Kannan

I have just tried the below:

%macro new2(ot=, SD=, ED=);

in the pass through sql:

WHERE
syndicate_share.syndicate_share_timestamp >= &SD
and syndicate_share.syndicate_share_timestamp <= &ED

%mend new2;

%new2 (ot=work.a, SD='01Oct2015', ED='31Oct2015') ;

IT's WORKING NOW!!!

Thanks you very much for reaching out.

Rgds

Rimon