BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6


proc sql;

create table temp as

 select a.*,b.Machine as b_Machine,b.date as b_date

  from a,b

   where a.Machine=b.Machine and b.date gt a.date

    group by a.Machine

     having b.date-a.date=min(b.date-a.date);


create table want as

 select temp.*,c.date as c_date,

    case 

     when c.date between temp.date and temp.b_date then 'yes'

     else 'no '

    end as flag                       

  from temp left join c on

   temp.Machine=c.Machine ;

quit;

My want table has a where statement where the c_date
Falls between temp date and temp date ....but it's not it's bringing in every instance from c table ...I don't need every instance from c only if it falls between those 2 dates an if not than blank..thanks
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Try this instead as your second query :

create table want as
 select temp.*,c.date as c_date,
    case 
     when c.date is missing then 'no'
     else 'yes'
    end as flag                       
  from temp left join c on
   temp.Machine=c.Machine and c.date between temp.date and temp.b_date;

(untested)

PG

View solution in original post

5 REPLIES 5
AndrewHowell
Moderator

I'm guessing either or both of:

  • Mixes of dates & datetimes
  • The Join failing to populate one of the date values

Difficult to diagnose without seeing the data. Can you either:

  • Post a subset of data so we can review, or;
  • Attempt to replicate the issue using one of the sample SAS data sets in the SASHELP library, which we could all then use to diagnose the issue.

 

Also, if your issue is specifically a CASE/BETWEEN issue, can I suggest you leave out the other statements (joins, create table, etc)

PGStats
Opal | Level 21

Try this instead as your second query :

create table want as
 select temp.*,c.date as c_date,
    case 
     when c.date is missing then 'no'
     else 'yes'
    end as flag                       
  from temp left join c on
   temp.Machine=c.Machine and c.date between temp.date and temp.b_date;

(untested)

PG
BETO
Fluorite | Level 6

the first query works it brings in 82 rows of data .I need to run it thru the 3rd table to see if there was a make up service  an when it runs it's bring in every instance from the 3rd table it increase output to 350 rows here is example
Output 1st query.                                                             Output 2nd query 
Machine.       Date.         Machine       date                     Machine      date.              flag 
A1.               09/05/15.      A1.            09/07/15.                 A1.            09/07/15.      yes
A1.               09/5/15.        A1.             09/07/15.                A1.           09/15/15.       No
A1.                09/05/15.     A1.             09/07/15.                 A1.           09/24/15.      No
It's bring every instance  from the 3rd table if it would have stop in the first  it would be perfect in that instance 
Here is and example of what I'm looking for 
Output 1st query.                                                             Output 2nd query 
Machine.       Date.         Machine       date                     Machine      date.                 Flag
A1.               09/05/15.      A1.            09/07/15.                 A1.            09/07/15.          Yes
A2.               09/09/15.      A2.            09/14/15.                 A2.            09/16/15.           No
A3.               09/2/15.        A3.             09/06/15.                A3.            09/04/15.           Yes
A1.               09/11/15.      A1              09/15/15.                A1.            09/18/15.            No

The first query works great I just need to run it thru 3rd. Table to make sure there was no make up service.. An what's happening it's bringing in everything from the 3rd table instance so if machine a1 appears 10x in 3rd table it brings all 10x over. I just need the one instance if it falls between those two dates if not. Than I don't need the other instance  to come with .... Thanks
PGStats
Opal | Level 21
As Andrew Howell suggested, please post a small sample of tables a, b, and c and the expected final output.
PG
BETO
Fluorite | Level 6
Hi PGSTATS ,
I provided an example above of 3 tables and there structure. ..the temp query works fine the want seems to bring everything in from the 3rd table ....thanks

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
  • 5 replies
  • 902 views
  • 0 likes
  • 3 in conversation