topic Re: Convert numeric yyyymm to sas date in SAS Programming

Convert numeric yyyymm to sas date

Sas_noob25 — Tue, 01 Nov 2022 16:32:50 GMT

Hello!

I have a date field in a numeric format as yyyymm (201603) and I'm trying to convert it to a date format ('01MAR2016'). The day doesn't matter as much because I'm only using this to be able to get 2016-03. And the only way I've found to get that format is by using a put statement for a date field.

Any help is greatly appreciated!

Re: Convert numeric yyyymm to sas date

Tom — Tue, 01 Nov 2022 16:40:41 GMT

So you have a number like 201,603 and you want to interpret it as some day in March of 2016?

You can convert it to a character string and then you could use an INFORMAT to convert it to a date value.

You could then attach the YYMMD7. format specification to it to make it display in YYYY-MM style.

data have;
  input number ;
cards;
201603
;

data want;
   set have;
   date_value = input(put(NUMBER,z6.),yymmn6.);
   format date_value yymmd7. ;
run;

Re: Convert numeric yyyymm to sas date

Kurt_Bremser — Tue, 01 Nov 2022 16:43:11 GMT

First of all, you don't have a date value. You have a number which looks like a date-related value, but is not in reality.

So you first must convert the number to a string, which can then be used as input for a function using a date informat.

Sunce you already have a numeric variable, you can convert "in place":

numvar = input(put(numvar,z6.),yymmn6.);
format numvar yymmd7.;

Re: Convert numeric yyyymm to sas date

Sas_noob25 — Tue, 01 Nov 2022 16:45:52 GMT

I tried this as well and I'm still getting blanks for my date2 field.

data clean;
set clean;
date2= input(put(date1,z6.),yymmdd6.);
format date2 yymmd7.;
run;

Re: Convert numeric yyyymm to sas date

Tom — Tue, 01 Nov 2022 16:59:55 GMT

You tried to read the string as if it had YYMMDD values. But your description said that the values only had YYYYMM values instead.

Use the YYMMN informat, not the YYMMDD informat.

Or append '01' and use the YYMMDD8. informat instead of YYMMDD6. informat.

Re: Convert numeric yyyymm to sas date

Kurt_Bremser — Tue, 01 Nov 2022 17:06:16 GMT

To become a successful coder, you MUST pay attention to details. You did not use the informat we both suggested.

Re: Convert numeric yyyymm to sas date

ballardw — Tue, 01 Nov 2022 17:58:56 GMT

@Sas_noob25 wrote:

I tried this as well and I'm still getting blanks for my date2 field.

data clean;
set clean;
date2= input(put(date1,z6.),yymmdd6.);
format date2 yymmd7.;
run;

A value of 201603 read with yymmdd6 would treat 20 as the year (i.e. 2020 with likely system settings), 16 as the month (the MM part) and 03 as the day. Since there is no month 16 you would generate an invalid data message similar to this:

NOTE: Invalid argument to function INPUT at line

with the line and column referencing where your INPUT function call was encountered.

Did you read the log? I suspect that if all your dates are later than the year 2012 that every record generated an invalid data message until your limit setting was reached. Which would explain why the values were missing.

Re: Convert numeric yyyymm to sas date

yabwon — Tue, 01 Nov 2022 19:09:16 GMT

Try this:

data number2date;
  n = 201603 ;

  format d date11.;
  d = MDY(mod(n,100),1,n/100);
run;

Bart