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..
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
Would it be possible to share the log ?
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....
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.