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 |
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.
It seems that the dates in your dataset are not sas dates but charachters ? is it ?
All the variables that is being used is Numeric type and YYMMDD10. format.
Even after converting to sas dates, the results are the same.
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;
This code gives the same result.
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.
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.
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.
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";
@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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.