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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.