How to use INTNX on a char field?

[PopCorn14]

data question;

   length  dateBegin dateEnd $100;

   dateBegin="2019-10-14"; dateEnd="2020-10-14"; output;

   dateBegin="2019-10-16"; dateEnd=""; output;

run;

Hi,

We have a huge table where 2 columns contain dates which are stored as char length 100 ( I know it's not the best but I can't change that).

They want to send us a formula which we could use and would mimic SQL function or feature DateAdd (ex:DateAdd (YYYY, 1, "2019-10-14") ).

So intnx does the same thing in a way with Dates.  However how can I use it to increment a year on a char field?  SAS is complaining as it is not a date datatype.

Formula sent to our table :  if missing(dateEnd) then intnx('year',dateBegin, 1);

Thank you.

[PaigeMiller]

You can't use INTNX on character variables. It can only be used on text numeric variables. So, make your variables numeric.

data question;
    dateBegin="14OCT2019"d; dateEnd="14OCT2020"d; output;
    dateBegin="16OCT2019"d; dateEnd=.; output;
run;

or convert them to numeric using the INPUT function

dateBegin="2019-10-14";
dateBegin_numeric=input(dateBegin,yymmdd10.);

[ballardw]

---

@PaigeMiller wrote:

You can't use INTNX on character variables. It can only be used on text variables. So, make your variables numeric.

data question;
    dateBegin="14OCT2019"d; dateEnd="14OCT2020"d; output;
    dateBegin="16OCT2019"d; dateEnd=.; output;
run;

or convert them to numeric using the INPUT function

dateBegin="2019-10-14";
dateBegin_numeric=input(dateBegin,yymmdd10.);

---

@PaigeMiller 

I think you meant to type "It can only be used on numeric variables." instead of "text variables".

[PaigeMiller]

---

@ballardw wrote:

---

@PaigeMiller wrote:

You can't use INTNX on character variables. It can only be used on text variables. So, make your variables numeric.

data question;
    dateBegin="14OCT2019"d; dateEnd="14OCT2020"d; output;
    dateBegin="16OCT2019"d; dateEnd=.; output;
run;

or convert them to numeric using the INPUT function

dateBegin="2019-10-14";
dateBegin_numeric=input(dateBegin,yymmdd10.);

---

@PaigeMiller 

I think you meant to type "It can only be used on numeric variables." instead of "text variables".

---

yes, that's what I meant, thanks!

[PopCorn14]

Thank you very much @PaigeMiller 

Sorry I am almost there ...at the end, my dateEnd has to be back into a char value.

There is something I am doing wrong in my conversion below so that I get the correct date;  now it is not incrementing by 1 year.

data question_result(drop=dateBegin_numeric);

   set question;

   dateBegin_numeric=input(dateBegin,yymmdd10.);

   if missing(dateEnd) then dateBegin_numeric=intnx('year',dateBegin_numeric, 1);

   /* almost there...but not the right result */

   dateEnd=put(dateBegin_numeric,yymmdd10.);

run;

[PaigeMiller]

---

@PopCorn14 wrote:

Thank you very much @PaigeMiller 

Sorry I am almost there ...at the end, my dateEnd has to be back into a char value.

---

Why does it have to be character? Dates in SAS are numeric!

There is something I am doing wrong in my conversion below so that I get the correct date;  now it is not incrementing by 1 year.

What is it doing? Explain.

[PopCorn14]

@PaigeMiller 

It's not my choice, the table is coming from a huge program, some manipulation will be done to it and then that table will be shipped to another program.  Therefore I cannot change the datatype.I will have to drop the column dateBegin_numeric after the conversion.

What I meant is the date in the second line "dateEnd" has not been incremented by a year.  See picture. 

[PopCorn14]

oups...wrong code

data question_result; /*(drop=dateBegin_numeric);*/
set question;
if missing(dateEnd) then do;
dateBegin_numeric=input(dateBegin,yymmdd10.);
dateBegin_numeric=intnx('year',dateBegin_numeric, 1);
/* almost there...but not the right result */
dateEnd=put(dateBegin_numeric,yymmdd10.);
end;
run;

[ballardw]

---

@PopCorn14 wrote:
oups...wrong code

data question_result; /*(drop=dateBegin_numeric);*/
set question;
if missing(dateEnd) then do;
dateBegin_numeric=input(dateBegin,yymmdd10.);
dateBegin_numeric=intnx('year',dateBegin_numeric, 1);
/* almost there...but not the right result */
dateEnd=put(dateBegin_numeric,yymmdd10.);
end;
run;

---

It really helps to describe what is "not right". Otherwise we have to guess.

I suspect that you want the yymmddX.w format. The X is a placeholder for a letter to indicate the desired separator between the values. If you want a dash or hyphen then yymmddd10. (yes 3 d's)

B

separates with a blank.

C

separates with a colon.

D

separates with a hyphen.

N

indicates no separator.

P

separates with a period.

S

separates with a slash.

[PopCorn14]

Thank you very much @ballardw. I always have issues with the format. I will keep your notes handy.

[PaigeMiller]

---

@PopCorn14 wrote:

@PaigeMiller 

It's not my choice, the table is coming from a huge program, some manipulation will be done to it and then that table will be shipped to another program.  Therefore I cannot change the datatype.I will have to drop the column dateBegin_numeric after the conversion.

What I meant is the date in the second line "dateEnd" has not been incremented by a year.  See picture. 

---

In SAS, you do the manipulations with a numeric variable. If you have to send it to another program as character (I'm skeptical that it requires character, what program?), then you use formats in SAS to make the variable appear as character.

[PopCorn14]

Thank You @PaigeMiller 

I know dates in SAS shouldn't be char etc......but client needs this change done on these 2 char fields.

What I meant is that we have a table with multiple columns (numeric and chars) which goes through multiple processing depending on different modules.  It happens that recently the client noticed that these 2 char fields need to be filled all the time.  These 2 fields need to stay char ..sorry but they have their reason for other programs.

Thank you for your solution with the input which made the use of intnx worked.  Now the only thing missing, is how can I get dateBegin_numeric back into dateBegin (char)  ? (I know it's terrible but I have no choice).    I must be doing something wrong with the "put" statement as the result doesn't show the date incremented by a year. The format is YYYY-MM-DD

data question_result; /*(drop=dateBegin_numeric);*/

   set question;

   if missing(dateEnd) then do;

     /*Solution from Paige*/

     dateBegin_numeric=input(dateBegin,yymmdd10.);

     dateBegin_numeric=intnx('year',dateBegin_numeric, 1);

     /* right format but it wasn't incremented by 1 year ?   */

     dateEnd=put(dateBegin_numeric,yymmdd10.);

   end;

run;

[andreas_lds]

You need to use the fourth parameter of intnx, it default to "beginning", but you need "same".

[PopCorn14]

I will keep that in mind. Thank you very much.

[Kurt_Bremser · Accepted solution]

data have;
infile datalines truncover;
input (date_begin date_end) (:$10.);
datalines;
2019-10-14 2020-10-14
2019-10-16
;

data want;
set have;
if missing(date_end)
then date_end = put(intnx('year',input(date_begin,yymmdd10.),1,'s'),yymmdd10.);
run;

proc print data=want noobs;
run;

Result:

date_begin	date_end
2019-10-14	2020-10-14
2019-10-16	2020-10-16