topic Re: create new variables with condition in SAS Programming

create new variables with condition

d0816 — Thu, 11 Apr 2019 14:40:06 GMT

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

Re: create new variables with condition

Shmuel — Thu, 11 Apr 2019 15:34:21 GMT

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

Re: create new variables with condition

PeterClemmensen — Thu, 11 Apr 2019 15:35:28 GMT

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;

Re: create new variables with condition

d0816 — Thu, 11 Apr 2019 15:43:46 GMT

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

Re: create new variables with condition

ballardw — Thu, 11 Apr 2019 15:44:02 GMT

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.

Re: create new variables with condition

d0816 — Thu, 11 Apr 2019 15:48:00 GMT

This code gives the same result.

Re: create new variables with condition

d0816 — Thu, 11 Apr 2019 15:50:33 GMT

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.

Re: create new variables with condition

d0816 — Thu, 11 Apr 2019 16:15:30 GMT

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

Re: create new variables with condition

Shmuel — Thu, 11 Apr 2019 16:15:42 GMT

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.

Re: create new variables with condition

ballardw — Thu, 11 Apr 2019 16:21:24 GMT

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

Re: create new variables with condition

d0816 — Thu, 11 Apr 2019 16:26:53 GMT

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";

Re: create new variables with condition

d0816 — Thu, 11 Apr 2019 16:33:34 GMT

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.