topic Re: Creating new variables conditional on monyy. dates in SAS Programming

Creating new variables conditional on monyy. dates

sasgorilla — Fri, 31 Jan 2025 10:36:16 GMT

I have a data set with a numeric month variable in monyy. format. I'm having trouble doing two things:

1) I want to create indicator variables that are 0 until a certain month, after which they become 1. The below code resulted in the included errors for any months I used. I tried using 'FEB2010'd as well with same result for all months attempted.

If month >= 'FEB10'd then x1=1; 
Else x1=0; 

/*Resulting Error*/
/*ERROR: Invalid date/time/datetime constant 'FEB10'd.
/*ERROR 77-185: Invalid number conversion on 'FEB10'd.

In my sample below I want x1 to become 1 from Feb2010 onward, and x2 to become 1 from Apr2010 onward.

2) I also want to create variables that count months starting at certain months. In the below sample I have a variable I want to start counting upward each month by 1 beginning in Mar2010. The code I tried using in the example below was count1=intnx('month', 'MAR2010'd, 1), but I receive the same error as detailed above.

I feel like this is a simple solution, but I am having trouble figuring it out. I always struggle with dates. Thank you for your help!

data have;
input month monyy. exp var_a;
format month monyy.;
datalines;
JAN10 1 100
JAN10 0 245
FEB10 1 75
FEB10 0 115
MAR10 1 100
MAR10 0 80
APR10 1 50
APR10 0 75
MAY10 1 100
MAY10 0 115
;
run;

data have;
input month monyy. exp var_a x1 /*1 from FEB2010*/ x2 /*1 from Apr2010*/ count1 /*starts counting from Mar2010*/;
format month monyy.;
datalines;
JAN10 1 100 0 0 0
JAN10 0 245 0 0 0
FEB10 1 75 1 0 0
FEB10 0 115 1 0 0
MAR10 1 100 1 0 1
MAR10 0 80 1 0 1
APR10 1 50 1 1 2
APR10 0 75 1 1 2
MAY10 1 100 1 1 3
MAY10 0 115 1 1 3
; 
run;

Re: Creating new variables conditional on monyy. dates

PaigeMiller — Fri, 31 Jan 2025 10:50:15 GMT

Question 1

'FEB10'd is not a valid SAS data literal. SAS does not understand this. Date literals MUST (as in, not optional, this is mandatory) be a two digit day followed by a 3 letter month, followed by a 2 or 4 digit year (and good practice means you will use a 4 digit year). No exceptions.

So you can write '01FEB2010'd, SAS would understand this. But even so, I cannot yet write code for you, because I don't know what the variable MONTH contains. If it contains numeric SAS date values, representing the number of days since 01JAN1960, formatted in any way you want, then the code is:

If month >= '01FEB2010'd then x1=1;

However, if the variable MONTH is character, or a numeric number which is not a valid SAS date value, such as 201002 (which could be interpreted by humans as February 2010 but would not be interpreted by SAS as February 2010), then I need specifics about what variable MONTH contains.

Question 2: Same problem, same answer, SAS does not understand 'MAR2010'd.

Re: Creating new variables conditional on monyy. dates

sasgorilla — Fri, 31 Jan 2025 10:50:42 GMT

@PaigeMiller Thanks for the quick response and the guidance! Changing my dates to '01FEB2010'd etc. worked for the 0/1 indicator values.

Knowing that, do you have any guidance on how I could code a counter variable from a specific date onward?

Re: Creating new variables conditional on monyy. dates

PaigeMiller — Fri, 31 Jan 2025 10:58:49 GMT

Problem 2

data want;
input month monyy. exp var_a x1 /*1 from FEB2010*/ x2 /*1 from Apr2010*/ ;
if month >= '01MAR2010'd then count1=intck('month','01MAR2010'd,month)+1; 
else count1=0;
format month monyy.;
datalines;
JAN10 1 100 0 0 0
JAN10 0 245 0 0 0
FEB10 1 75 1 0 0
FEB10 0 115 1 0 0
MAR10 1 100 1 0 1
MAR10 0 80 1 0 1
APR10 1 50 1 1 2
APR10 0 75 1 1 2
MAY10 1 100 1 1 3
MAY10 0 115 1 1 3
;

Re: Creating new variables conditional on monyy. dates

sasgorilla — Fri, 31 Jan 2025 11:03:25 GMT

@PaigeMiller Thank you! That worked perfectly. I have several counter variables I will apply it to and let you know if I have any other questions. I really appreciate your quick help!

Re: Creating new variables conditional on monyy. dates

Tom — Fri, 31 Jan 2025 15:05:34 GMT

Looks like you are trying to calculate three new variables. Two boolean flags and one running count. So you just need to two assignment statements and one sum statement.

data want;
  set have;
  by month;
  x1 = month >= '01FEB2010'd;
  x2 = month >= '01APR2010'd;
  count1 + first.month and (month >= '01MAR2010'd);
run;

And if the data is not sorted (or has missing months) then just use INTNX() to count the number of months.

count1 = max(0,intck('month','01FEB2010'd,month));