topic Re: How to convert number or string to date with yyyymm format and create new date field based on it in SAS Programming

How to convert number or string to date with yyyymm format and create new date field based on it

LL5 — Wed, 29 May 2024 15:14:24 GMT

Hi, I am trying to convert a six-digit number or string to a date with yyyymm format, then create two (or potentially) more new date variables, one is 12 months prior to that date, and another is 24 months prior to that date.

Below is the example. Let's say I have a pre-define date in yyyymm format (i.e 202312 for December 2023), I want to

1) convert this 202312 to a date. Since at this time I am not 100% sure if data will flow in from upstream process as a string or a numeric value, I tried to convert to both numeric and character (using input and put function, respectively)

2) create two more new fields. dt12 is 12 months prior to 202312, which would be 202301. dt24 is 24 months prior to 202312, which would be 202201

3) these new fields will be used as a filter in the where statement to extract subset of data within specific time period.

I was not able to create dt12 and dt24, any advice or guidance will be greatly appreciated.

%let yyyymm_character='202312';
%let yyyymm_numeric=202312;
data test;
dt_numeric = input(&yyyymm_numeric,YYMMNw.);
dt_character = put(&yyyymm_numeric,YYMMNw.);
dt12 = intnx('month',dt_numeric,-12,'e');
dt24 = intnx('month',dt_numeric,-24,'e');
run;

Re: How to convert number or string to date with yyyymm format and create new date field based on it

PaigeMiller — Wed, 29 May 2024 15:49:08 GMT

%let yyyymm_character='202312';
%let yyyymm_numeric=202312;
data test;
dt_numeric = input(&yyyymm_character,YYMMN6.);
dt_character = put(&yyyymm_numeric,6.);
dt12 = intnx('month',dt_numeric,-12,'e');
dt24 = intnx('month',dt_numeric,-24,'e');
format dt_numeric dt12 dt24 yymmn6.;
run;

Format names never end with W, the W indicates width, which must be a number, or can be left out. Your original code tried to INPUT &yyyymm_numeric, but INPUT works on character strings.

Also, since I dislike macro variables that have quotes around the values (I feel it is unnecessary and can cause problems in some situations), this works too

%let yyyymm=202312;
data test;
dt_numeric = input("&yyyymm",YYMMN6.);
dt_character = put(&yyyymm,6.);
dt12 = intnx('month',dt_numeric,-12,'e');
dt24 = intnx('month',dt_numeric,-24,'e');
format dt_numeric dt12 dt24 yymmn6.;
run;

Re: How to convert number or string to date with yyyymm format and create new date field based on it

yabwon — Wed, 29 May 2024 15:45:10 GMT

%let yyyymm_character='202312';
%let yyyymm_numeric=202312;
data test;

numeric=&yyyymm_numeric.;

character=&yyyymm_character.;

/* NUMERIC case */
dt_numeric = MDY(mod(numeric,100),01,int(numeric/100));

dt_numeric12 = intnx('month',dt_numeric,-12,'e');
dt_numeric24 = intnx('month',dt_numeric,-24,'e');

put (dt_numeric:) (/=date11.);




/* CHARACTER case */
dt_character = input(character !! '01',YYMMdd10.);
dt_character12 = intnx('month',dt_character,-12,'e');
dt_character24 = intnx('month',dt_character,-24,'e');

put (dt_character:) (/=date11.);

run;

Re: How to convert number or string to date with yyyymm format and create new date field based on it

ballardw — Wed, 29 May 2024 16:02:09 GMT

You did not provide a width, number of characters for the YYMMN informat to use so nothing was performed. Your log would show something like

966  data test;
967  dt_numeric = input(&yyyymm_numeric,YYMMNw.);
                                        -------
                                        485
968  dt_character = put(&yyyymm_numeric,YYMMNw.);
                                        -------
                                        484
NOTE 485-185: Informat YYMMNW was not found or could not be loaded.

NOTE 484-185: Format YYMMNW was not found or could not be loaded.

969  dt12 = intnx('month',dt_numeric,-12,'e');
970  dt24 = intnx('month',dt_numeric,-24,'e');
971  run;

Not loaded means nothing was done because the informat could not be found.

Also, INPUT function requires character values. If you give SAS a numeric value then it does a default numeric to character conversion that is generally using a BEST12 format resulting in 6 leading spaces that the YYMMN informat will not use as you might want.

This is what your log would show if you provided the proper width value for the informat:

975  dt_numeric = input(&yyyymm_numeric,YYMMN6.);
976  /*dt_character = put(&yyyymm_numeric,YYMMNw.);*/
977  dt12 = intnx('month',dt_numeric,-12,'e');
978  dt24 = intnx('month',dt_numeric,-24,'e');
979  run;

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      975:20
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 977:8   1 at 978:8

And the result is missing values because of the conversion.

So use the CHARACTER version.

Note that I use a DATE9 format so you can see the result below.

%let yyyymm_character='202312';
data test;
dt_numeric = input(&yyyymm_character,YYMMN6.);
dt12 = intnx('month',dt_numeric,-12,'e');
dt24 = intnx('month',dt_numeric,-24,'e');
format dt_numeric dt12 dt24 date9.;
run;

Note: your attempt to display a simply number number, which is what 202312 is, with a date format would not show anything resembling a desired date. SAS dates are number of days since 01JAN1960. So add 20,312 days and you get a date in year 2513.

Re: How to convert number or string to date with yyyymm format and create new date field based on it

LL5 — Wed, 29 May 2024 18:45:31 GMT

@PaigeMiller Thanks a lot for the explanation of my code error and providing a solution. Really appreciate for your insights and advice.

Re: How to convert number or string to date with yyyymm format and create new date field based on it

LL5 — Wed, 29 May 2024 18:46:46 GMT

@yabwon Thanks for illustrating the solution for both numeric and character value. Much appreciated it.

Re: How to convert number or string to date with yyyymm format and create new date field based on it

LL5 — Wed, 29 May 2024 18:48:00 GMT

@ballardw Thanks a lot for analyzing the log message line by line, as well as providing a solution along with a detail explanation. Really helpful and greatly appreciated.