topic Re: How to use INTNX on a char field? in SAS Programming

How to use INTNX on a char field?

PopCorn14 — Mon, 26 Apr 2021 13:49:46 GMT

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.

Re: How to use INTNX on a char field?

PaigeMiller — Mon, 26 Apr 2021 14:43:39 GMT

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.);

Re: How to use INTNX on a char field?

ballardw — Mon, 26 Apr 2021 14:40:53 GMT

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

Re: How to use INTNX on a char field?

PaigeMiller — Mon, 26 Apr 2021 14:43:10 GMT

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

Re: How to use INTNX on a char field?

PopCorn14 — Mon, 26 Apr 2021 15:10:40 GMT

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;

Re: How to use INTNX on a char field?

PaigeMiller — Mon, 26 Apr 2021 15:14:02 GMT

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

Re: How to use INTNX on a char field?

PopCorn14 — Mon, 26 Apr 2021 16:12:00 GMT

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

Re: How to use INTNX on a char field?

PopCorn14 — Mon, 26 Apr 2021 16:20:11 GMT

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;

Re: How to use INTNX on a char field?

ballardw — Mon, 26 Apr 2021 16:28:30 GMT

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

Re: How to use INTNX on a char field?

PaigeMiller — Mon, 26 Apr 2021 16:31:02 GMT

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

Re: How to use INTNX on a char field?

Kurt_Bremser — Mon, 26 Apr 2021 18:08:11 GMT

You do not (as in NOT) store dates in this way in SAS. You simply don't.

SAS dates are counts of days, starting with 1960-01-01 as day zero, stored as numbers, with a date format attached so that the value is human-readable.

If you need to export the dates to another application in a specific format, this is done when the export happens, but while you work with the data in SAS, dates have to be stored as numbers, as described above. Otherwise you deprive yourself of all the nice tools SAS provides out of the box for working with such data.

Since the numeric values of dates are in a quite limited range, you can use a length of 4 for the numeric variables, so you also have a significant saving in terms of space.

Re: How to use INTNX on a char field?

PopCorn14 — Mon, 26 Apr 2021 18:40:24 GMT

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;

Re: How to use INTNX on a char field?

andreas_lds — Tue, 27 Apr 2021 07:19:31 GMT

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

Re: How to use INTNX on a char field?

Kurt_Bremser — Tue, 27 Apr 2021 07:50:56 GMT

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

Re: How to use INTNX on a char field?

PopCorn14 — Tue, 27 Apr 2021 20:52:52 GMT

@Kurt_Bremser Alleluia. Thank you so much. That is what I wasn't able to get.

Like I said, I know that dates are NOT supposed to be stored in char. It is a specific request from clients on a huge table containing just a few char columns. This table is then sent to another huge program that will transpose etc...therefore I am NOT in position to change the length on any columns etc...Sometimes we need to do strange things.

Re: How to use INTNX on a char field?

PopCorn14 — Tue, 27 Apr 2021 20:55:23 GMT

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

Re: How to use INTNX on a char field?

PopCorn14 — Tue, 27 Apr 2021 20:56:10 GMT

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

Re: How to use INTNX on a char field?

ChrisNZ — Tue, 27 Apr 2021 21:21:28 GMT

> a formula which we could use and would mimic SQL function

What do you use the formula for? Create a new table? The result is a SAS date field? Is the formula is a data step or a SQL query?