topic Re: Extracting Year and Month from Date field issue in SAS Programming

Extracting Year and Month from Date field issue

greg6363 — Wed, 04 Mar 2020 23:13:42 GMT

I'm trying to create a new field in PROC SQL that uses just the year and month (YYYYMM - ex. 201812) from a date field with the format YYYYMMDD (ex. 20181204) but when I attempt to apply the format, I keep getting the following error message:

ERROR: There was a problem with the format so BEST. was used.

The output shows as scientific notation (2.04E27)

This is how I wrote the code:

proc sql;

create table_master as

select revised_date as revised_month format yymm6.

from primary_table;

quit;

The purpose is for the new field will be to group totals by month. What am I doing wrong?

Any feedback is greatly appreciated. Thanks.

Re: Extracting Year and Month from Date field issue

Reeza — Wed, 04 Mar 2020 22:55:34 GMT

select as revised_date as revised_month format = yymmn6.

What about that?

Delete 'as'

add equal sign (may not be required)

Change format to yymmn6 so that it matches your explanation.

@greg6363 wrote:

I'm trying to create a new field in PROC SQL that uses just the year and month (YYYYMM - ex. 201812) from a date field with the format YYYYMMDD (ex. 20181204) but when I attempt to apply the format, I keep getting the following error message:

ERROR: There was a problem with the format so BEST. was used.

The output shows as scientific notation (2.04E27)

This is how I wrote the code:

proc sql;

create table_master as

select as revised_date as revised_month format yymm6.

from primary_table;

quit;

The purpose is for the new field will be to group totals by month. What am I doing wrong?

Any feedback is greatly appreciated. Thanks.

Re: Extracting Year and Month from Date field issue

greg6363 — Wed, 04 Mar 2020 23:17:53 GMT

I made the corrections you noted but I still receive the same error:

ERROR: There was a problem with the format so BEST. was used.

Here is the output that gets generated for each observation: 2.02E7

???

Re: Extracting Year and Month from Date field issue

ballardw — Wed, 04 Mar 2020 23:24:55 GMT

@greg6363 wrote:

I'm trying to create a new field in PROC SQL that uses just the year and month (YYYYMM - ex. 201812) from a date field with the format YYYYMMDD (ex. 20181204) but when I attempt to apply the format, I keep getting the following error message:

ERROR: There was a problem with the format so BEST. was used.

The output shows as scientific notation (2.04E27)

This is how I wrote the code:

proc sql;

create table_master as

select revised_date as revised_month format yymm6.

from primary_table;

quit;

The purpose is for the new field will be to group totals by month. What am I doing wrong?

Any feedback is greatly appreciated. Thanks.

One suspects that your base variable, revised_date is actually numeric of value 20181204 and not a SAS date value corresponding to the same date that actually accepts date formats.

What is the current format assigned to the variable REVISED_DATE?

You may want to consider this code:

data junk;
   x=20181204;
   put 'Formatted numeric 20181204: ' x= yymmn6.;
   y = '04DEC2018'd;
   put 'Numeric value of 04DEC2018 SAS Date value: ' y= best8.;

run;

The formatted value for X is displayed with ****** because 20181204 would represent a "date" if basically a numeric value WAY in the future. The date of 01JAN20000, yes year 20,000 has a numeric value of 6588971 and is way smaller than 20181204. The SAS date formats will only display years of 4 digits and the various date functions will give up past year 20000.

So you may need to create an actual SAS date value with something like

input(put(revised_date, best8. -L),yymmdd8.) as revised_month

Re: Extracting Year and Month from Date field issue

Reeza — Thu, 05 Mar 2020 02:17:16 GMT

If you have a date time variable, which has a time component, you need to first use DATEPART() to get just the date part and then apply the format.

Re: Extracting Year and Month from Date field issue

Kurt_Bremser — Thu, 05 Mar 2020 08:00:11 GMT

@greg6363 wrote:

Here is the output that gets generated for each observation: 2.02E7

That is not a SAS date value, but a number in the range 202xxxxx. Go back to the step that brought the data into SAS and make sure that this particular column is correctly imported.

If your data source contains dates without delimiters ("20200305"), proc import will mis-guess this for a simple number.