topic Re: How to add additional rows to the and statements? in SAS Procedures

How to add additional rows to the and statements?

Beto16 — Sun, 07 Feb 2016 13:31:46 GMT

I posted this question before I closed that post because after reading my question it was comfusing ..my apologies Enclose is a table that I've what I need is to make item C and D and statements below (IE GT,LT EQ) Thanks for your help and understanding I'm using SAS9.4
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

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 want as c on a.site=c.site want as d on a.site=D. 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;

Re: How to add additional rows to the and statements?

Reeza — Sun, 07 Feb 2016 13:47:44 GMT

Without seeing your macro definitions this doesn't help.

Please post your macro definitions and any errors you're receiving.

Your current code doesn't show any WHERE conditions, so you should just be able to add more conditions to the end. If you're running into issues doing so, you'll have to really explain what you're trying to do.

Re: How to add additional rows to the and statements?

LinusH — Sun, 07 Feb 2016 14:35:51 GMT

You have probably confused PROC with %macro, otherwise I can't get your code together.

If that's SQL that you are generating I miss select statement and probably you wish to store the result in a table, which means you need a create table statement.

Where did you get this code, or how did you develop it?
Like @Reeza I think you need to share some more details, like a log.

Re: How to add additional rows to the and statements?

ballardw — Mon, 08 Feb 2016 20:04:57 GMT

Please post example input data FOR ALL VARIABLES INVOLVED, desired output and rules if any.

You have an example of comparing one apparent time range with another. But now you want to add in at least two more comparisons without saying what you want to compare. Is the A range compared with C before or after comparing with B? Is A compared with D before or after comparing with B or C? Is B compared to either C, D or Both? Or is A compared to C before B... You have a LARGE number of potential cases to address.

Generally the solution is likely to involve putting entire subqueries in paranthesis to get the order correct and not just "adding AND clause members.

The presence of variables named mintime, mintime2, maxtime, maxtime2 (and do you also have a mintime3, mintime4, maxtime3 and maxtime4 that are to be used in the comparisons for C and D?) tend to make me believe that you have data at least partially in a "wide" format. Which may work better in a long form with mintime, maxtime and a period of measure variable such as 1,2,3,4 ....

Re: How to add additional rows to the and statements?

Beto16 — Tue, 09 Feb 2016 20:57:59 GMT

Here is my log when I run it
%macro r (x,n);&x. As &x.&n.%mend;
Pro sql;
Create table want as select
A.site,a.date,a.%r (site,1),a.%r (mintime,1),a.%r (maxtime,1),a.%r (durtime,1),
symbolgen: macro variable X resolves to site1
symbolgen: macro variable X resolves to site1
symbolgen: macro variable N resolves to 1
symbolgen: macro variable X resolves to maxtime1
symbolgen: macro variable X resolves to maxtime1
symbolgen: macro variable N resolves to 1
symbolgen: macro variable X resolves to mintime1
symbolgen: macro variable X resolves to mintime1
symbolgen: macro variable N resolves to 1
symbolgen: macro variable X resolves to durtime1
symbolgen: macro variable X resolves to durtime1
symbolgen: macro variable N resolves to 1
B.site,b.date,b.%r (site,2),b.%r (mintime,2), b.%r (maxtime,2),b.%r (durtime,2
symbolgen: macro variable X resolves to site2
symbolgen: macro variable X resolves to site2
symbolgen: macro variable N resolves to 2
symbolgen: macro variable X resolves to maxtime2
symbolgen: macro variable X resolves to maxtime2
symbolgen: macro variable N resolves to 2
symbolgen: macro variable X resolves to mintime2
symbolgen: macro variable X resolves to mintime2
symbolgen: macro variable N resolves to 2
symbolgen: macro variable X resolves to durtime2
symbolgen: macro variable X resolves to durtime3
symbolgen: macro variable N resolves to 2
From want6 as a inner join want6 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;

The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
the table want5 created with 3952 rows 10 columns

Re: How to add additional rows to the and statements?

Beto16 — Tue, 09 Feb 2016 20:59:09 GMT

Sorry durtime should be 2..I can't copy the log I have to type it

Re: How to add additional rows to the and statements?

Beto16 — Tue, 09 Feb 2016 21:07:38 GMT

Hi Balladw
Your right I want to add up 2 two additional variables sometimes it occurs that there are 2 or 3 or even 4 events ..... i would like to determine if 2 events had time overlap from the mintime ' maxtime of each events if time of events don't overlap that we can exclude...so if time overlaps d with a I don't need the other events hope that helps