BookmarkSubscribeRSS Feed
Beto16
Obsidian | Level 7

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;

 

6 REPLIES 6
Reeza
Super User

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.

 

LinusH
Tourmaline | Level 20
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.
Data never sleeps
ballardw
Super User

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

Beto16
Obsidian | Level 7
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



Beto16
Obsidian | Level 7
Sorry durtime should be 2..I can't copy the log I have to type it
Beto16
Obsidian | Level 7
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1264 views
  • 0 likes
  • 4 in conversation