DATA Step, Macro, Functions and more

SAS Proc sql pass through (postgressql) - macro date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

SAS Proc sql pass through (postgressql) - macro date

[ Edited ]

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..


Accepted Solutions
Solution
‎11-30-2015 12:24 AM
Occasional Contributor
Posts: 5

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

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

 

 

View solution in original post


All Replies
Regular Contributor
Posts: 161

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

Would it be possible to share the log ?

Kannan Deivasigamani
Regular Contributor
Posts: 161

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

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....

Kannan Deivasigamani
Solution
‎11-30-2015 12:24 AM
Occasional Contributor
Posts: 5

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

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 2 in conversation