topic Re: How to add additional tables to where statement in SAS Procedures

How to add additional tables to where statement

Beto16 — Fri, 05 Feb 2016 04:44:37 GMT

Hi I have a macro that looks like this

From want as a inner join want as b on a.site =b.site
And (a.maxtime1 GT b.mintime2
And
A.maxtime1 LT b.maxtime2
)
Or (a.maxtime1 EQ B.maxtime2
and
Input (substr (a.site11,3,4),4.) LT Input (substr (b.site11,3,4),4.)

Quit;

What I want to do is add C and D options to it
If you need additional info let me know

TIA

Re: How to add additional tables to where statement

Reeza — Fri, 05 Feb 2016 04:54:21 GMT

I don't understand your question. Please clarify, with as much details as possible.

Code/Log are appreciated, especially if you're getting errors.

Re: How to add additional tables to where statement

Beto16 — Fri, 05 Feb 2016 06:13:27 GMT

The macro looks like this
Proc r (x,n);x. As &x.&n. %mend;
A.site,a.date,a.%r (site,1),a.%r (mintime,1),a.%r (maxtime,1),a.%r (durtime,1),
B.site,b.date,b.%r (site,2),b.%r (mintime,2), b.%r (maxtime,2),b.%r (durtime,2)

What I want to do is add this here

C.site,c.date,c.%r (site,3),c.%r (mintime,3),c.%r (maxtime,3),c.%r (durtime,3),

D.site,d.date,d.%r (site,4),d.%r (mintime,4),d.%r (maxtime,4),d.%r (durtime,4

From want as a inner join want as b on a.site =b.site
And (a.maxtime1 GT b.mintime2
And
A.maxtime1 LT b.maxtime2
)
Or (a.maxtime1 EQ B.maxtime2
and
Input (substr (a.site11,3,4),4.) LT Input (substr (b.site11,3,4),4.)

Quit;

I don't know how to add C and D. To make it part to section greater than LT than ... thank you

Re: How to add additional tables to where statement

LinusH — Fri, 05 Feb 2016 06:16:51 GMT

I can't see any macro components in your code. Is that a part of your question.

Is the question that you wish to use where as a join criteria. You could, and I suggest that you use one join syntax in your query. Mixing (between where and on) makes it hard to read and may potentially bring undesired results.

Re: How to add additional tables to where statement

Beto16 — Fri, 05 Feb 2016 06:50:48 GMT

Hi Linus
Your right it's not a macro I notice the mend an thought it was one
Your also right it's not a where statement my question us can we add additional something like this
C.site,c.date,c.%r (site,3),c.%r (mintime,3),c.%r (maxtime,3),c.%r (durtime,3),

D.site,d.date,d.%r (site,4),d.%r (mintime,4),d.%r (maxtime,4),d.%r (durtime,4)

An refer the C and D in the GT LT

The proc table works when it involves two variables but it applies 2 more it doesn't pick them up...what the proc table does is it take the 2 variables an determine if there mintime and maxtime overlaps between the other mintime and maxtime group site ..thanks again

Re: How to add additional tables to where statement

LinusH — Fri, 05 Feb 2016 07:01:38 GMT

I'm not aware of proc table.
First get a code working without macro.
Then troubleshoot your macro logic by using mprint mlogic symbolgen and %put.
If still can't solve it return with full code and log.

Re: How to add additional tables to where statement

Beto16 — Fri, 05 Feb 2016 07:10:57 GMT

The proc table I was referring is this

Proc r (x,n);x. As &x.&n. %mend;
A.site,a.date,a.%r (site,1),a.%r (mintime,1),a.%r (maxtime,1),a.%r (durtime,1),
B.site,b.date,b.%r (site,2),b.%r (mintime,2), b.%r (maxtime,2),b.%r (durtime,2)