Extracting Year and Month from Date field issue

[greg6363]

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.

[Reeza]

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.

---

[greg6363]

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

???

[Reeza]

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.

[Kurt_Bremser]

---

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

[ballardw]

---

@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