topic query to get where date matches in SAS Programming

query to get where date matches

radha009 — Thu, 17 Oct 2019 20:35:22 GMT

I want to get the data where date (table 1) matches in the table 2. it show no rows.

proc sql;
create table sh1.deployments as
select * from sh1.table2 where Start_Date in (select Date from sh1.table1 where 'Friday');
quit;

table1

day	Date
Friday	10/18/2019
Saturday	10/19/2019
Sunday	10/20/2019
Monday	10/21/2019

table 2:

Request#	Release ID	Analyst	Start Date	App ID	State
ABC3191089	REL0046421	Peter	10/18/2019	AD4234	Review
ABC3204276	REL0046421	Sam	10/18/2019	SD4344	Draft
ABC3212993	REL0047406	Robert	11/13/2019	IR4324	Review
ABC3213945	REL0046421	Peter	10/31/2019	Ar3345	Draft
ABC3217910	REL0048398	Sam	10/20/2019	FR3444	Draft
ABC3219508	REL0048584	Robert	10/25/2019	JY3434	Approval
ABC3221591	REL0048624	Peter	10/19/2019	AD4234	Approval
ABC3221593	REL0048625	Sam	10/26/2019	AD4234	Approval
ABC3221596	REL0048628	Robert	10/19/2019	SD4344	Draft
ABC3221612	REL0048629	Peter	11/23/2019	AD4234	Draft
ABC3222870	REL0048624	Sam	10/18/2019	AD4234	Approval
ABC3222872	REL0048628	Robert	11/15/2019	SD4344	Draft

Re: query to get where date matches

Kurt_Bremser — Thu, 17 Oct 2019 20:38:33 GMT

Your where condition in the sub-select is wrong. Replace

"Friday"

with

day = "Friday"

Re: query to get where date matches

radha009 — Thu, 17 Oct 2019 21:14:58 GMT

its typo i had

proc sql;
create table sh1.table3 as
select * from sh1.table2 where Start_Date in (select date from sh1.table1 where day='Friday');
quit;

Re: query to get where date matches

Kurt_Bremser — Tue, 22 Oct 2019 06:09:33 GMT

Then you have to look at the data.

Is day actually character, or could it be a number formatted to display day names?

Are your dates integer values? If imported from Excel, you might have fractions indicating times hidden.

PS: check the spelling of your day variable. 'friday' <> 'Friday'!

Re: query to get where date matches

ballardw — Fri, 18 Oct 2019 21:43:33 GMT

When I create data based on your pasted text and run a similar query I get 6 rows in the result.

data work.table1;
input day $ Date :mmddyy10.;
format date mmddyy10.;
datalines;
Friday 10/18/2019 
Saturday 10/19/2019 
Sunday 10/20/2019 
Monday 10/21/2019 
;
 

data work.table2;
   input Request :$10. ReleaseID :$10. Analyst $ Start_date :mmddyy10. AppID $ State $;
   format Start_date mmddyy10.;
datalines;
ABC3191089 REL0046421 Peter 10/18/2019 AD4234 Review 
ABC3204276 REL0046421 Sam 10/18/2019 SD4344 Draft 
ABC3212993 REL0047406 Robert 11/13/2019 IR4324 Review 
ABC3213945 REL0046421 Peter 10/31/2019 Ar3345 Draft 
ABC3217910 REL0048398 Sam 10/20/2019 FR3444 Draft 
ABC3219508 REL0048584 Robert 10/25/2019 JY3434 Approval 
ABC3221591 REL0048624 Peter 10/19/2019 AD4234 Approval 
ABC3221593 REL0048625 Sam 10/26/2019 AD4234 Approval 
ABC3221596 REL0048628 Robert 10/19/2019 SD4344 Draft 
ABC3221612 REL0048629 Peter 11/23/2019 AD4234 Draft 
ABC3222870 REL0048624 Sam 10/18/2019 AD4234 Approval 
ABC3222872 REL0048628 Robert 11/15/2019 SD4344 Draft 
;


proc sql;
   create table work.deployments as
   select * from work.table2 
   where Start_date in (select Date from work.table1 where  'Friday');
quit;

If I use a likely more correct Sql code:

proc sql;
   create table work.deployments as
   select * from work.table2 
   where Start_date in (select Date from work.table1 where day= 'Friday');
quit;

I get 3 rows.

I make very sure that your Start_date and Date variables are both actually date values, with no decimal portion as @Kurt_Bremser mentions may happen with data starting from some sources. Or given some folks having a poor habit of referring to datetime values as "dates", make sure that is not the case here. You may have to print some values using a BEST format to check.

If the dates are actually character variables then check closely on there values as well.

Also make sure that in your data set the variable Day does not have any leading spaces or trailing non-blank characters (null characters for example)