create new variables with condition

[d0816]

Hi,

I am creating new variable where During_college is a Yes if Class Start Date is at or in between college start and end date. Likewise, I am creating another variable where After_College is a Yes if Class Start Date is one day after College End Date. But the code is working for some ID and not working for others (See Yes in Red). Anyone has any insight why is this?

Code I am using:

data want;

set want;

if College_Start_Date <= Class_Start_Date<=College_End_Date then During_College="Yes";

run;

data want;

set want;

if Class_Start_Date>College_End_Date then After_College="Yes";

run;

ID	College_Start_Date	College_End_Date	Class_Start_Date	Class_End_Date	During_College	After_College
1	3/8/2017	3/12/2017	1/18/2018			Yes
2	10/16/2017	11/30/2017	5/7/2018			Yes
2	12/4/2017	4/30/2018	5/7/2018			Yes
2	10/16/2017	11/30/2017	5/7/2018			Yes
2	12/4/2017	4/30/2018	5/7/2018			Yes
3	3/20/2018	4/23/2018	4/8/2018		Yes
3	8/17/2017	11/30/2017	4/8/2018			Yes
3	3/20/2018	4/23/2018	4/8/2018		Yes
3	8/17/2017	11/30/2017	4/8/2018			Yes
4	3/1/2017	5/15/2017	5/8/2017		Yes
4	10/12/2016	2/28/2017	5/8/2017			Yes
4	3/1/2017	5/15/2017	10/12/2016		Yes
4	10/12/2016	2/28/2017	10/12/2016		Yes
5	4/27/2018	9/14/2018	8/13/2018	10/2/2018	Yes
6	4/27/2016	6/20/2016	2/25/2017			Yes
7	7/27/2016	4/27/2017	9/12/2016		Yes
8	3/1/2017	5/31/2017	11/4/2016	11/23/2016	Yes
8	11/12/2016	2/28/2017	11/4/2016	11/23/2016	Yes
9	12/6/2016	4/30/2017	2/6/2017		Yes
9	9/5/2016	11/30/2016	2/6/2017			Yes
9	6/15/2016	6/30/2016	2/6/2017			Yes
10	11/4/2016	2/3/2017	12/13/2016		Yes
10	11/4/2016	2/3/2017	12/1/2016		Yes
11	9/1/2017	10/29/2017	8/25/2017		Yes
12	4/6/2017	4/30/2017	4/24/2017	4/28/2017	Yes

[Shmuel]

It seems that the dates in your dataset are not sas dates but charachters ? is it ?

[d0816]

All the variables that is being used is Numeric type and YYMMDD10. format.

[d0816]

Even after converting to sas dates, the results are the same.

[PeterClemmensen]

data have;
input ID (College_Start_Date College_End_Date Class_Start_Date Class_End_Date)(:mmddyy10.);
format College_Start_Date College_End_Date Class_Start_Date Class_End_Date mmddyy10.;
infile datalines missover;
datalines;
1 3/8/2017 3/12/2017 1/18/2018 
2 10/16/2017 11/30/2017 5/7/2018 
2 12/4/2017 4/30/2018 5/7/2018 
2 10/16/2017 11/30/2017 5/7/2018 
2 12/4/2017 4/30/2018 5/7/2018 
3 3/20/2018 4/23/2018 4/8/2018 
3 8/17/2017 11/30/2017 4/8/2018 
3 3/20/2018 4/23/2018 4/8/2018 
3 8/17/2017 11/30/2017 4/8/2018 
4 3/1/2017 5/15/2017 5/8/2017 
4 10/12/2016 2/28/2017 5/8/2017 
4 3/1/2017 5/15/2017 10/12/2016 
4 10/12/2016 2/28/2017 10/12/2016 
5 4/27/2018 9/14/2018 8/13/2018 10/2/2018 
6 4/27/2016 6/20/2016 2/25/2017 
7 7/27/2016 4/27/2017 9/12/2016 
8 3/1/2017 5/31/2017 11/4/2016 11/23/2016 
8 11/12/2016 2/28/2017 11/4/2016 11/23/2016 
9 12/6/2016 4/30/2017 2/6/2017 
9 9/5/2016 11/30/2016 2/6/2017 
9 6/15/2016 6/30/2016 2/6/2017 
10 11/4/2016 2/3/2017 12/13/2016 
10 11/4/2016 2/3/2017 12/1/2016 
11 9/1/2017 10/29/2017 8/25/2017 
12 4/6/2017 4/30/2017 4/24/2017 4/28/2017
;

data want;
 set have;
 if College_Start_Date <= Class_Start_Date<=College_End_Date then During_College="Yes";
 else if Class_Start_Date>College_End_Date then After_College="Yes";
run;

[d0816]

This code gives the same result.

[ballardw]

Reread your dates.

You indicate this one should be "yes"

4 3/1/2017 5/15/2017 10/12/2016

but the year of the class start is clearly before the college start.

You have similar issues with all of your shown red examples: the Class start date is NOT in the interval.

[d0816]

Yes is the result of using the code. and that's what I am trying to figure out that it should not be yes. All the red "Yes" es is where the code is not working.

[Shmuel]

run next code and check the log:

data a;
 date_start = '01mar2017'd;
 date_end   = '15may2017'd;
 date_test  = '12oct2016'd;
 
 if date_start <= date_test <= date_end 
     then put 'YES' ; else put "NO";

date_x1 = put(date_start,mmddyy10.);
date_x2 = put(date_end,mmddyy10.);
date_tx = put(date_test,mmddyy10.);

if date_x1 <= date_test <= date_end
     then put 'YES' ; else put "NO";

run;

when dates are sas numeric dates the result isd NO,

while when the dates are char typy in mmddyy10. format the rtesult is YES

and the log contains the note: Invalid numeric data ...

Check again what type are your dates and check your log. 

[d0816]

I tried by adding the following in my original two codes,  and it worked. I think this was the missing piece in the code. But still if these were not added it should have been just blank instead of yes. That part I did not get. but now the code works properly.

else During_College="No";

else After_College="No";

[ballardw]

---

@d0816 wrote:

Yes is the result of using the code. and that's what I am trying to figure out that it should not be yes. All the red "Yes" es is where the code is not working.

---

When I ran the code with SAS date values NONE of these were marked as YES.

So apparently you do not have SAS date values and possibly something else is happening from the repeated use of the

data have;

   set have;

coding.

Each of those steps will completely replace an existing data set and once you have an logic error you may not be overwriting previous results properly.

[d0816 · Accepted solution]

I did a fresh run and you were right. such a rookie mistake.

It works even when not in sas date value. My date variables were in numeric and YYMMDD10. format.

Thank you for the solution.