BookmarkSubscribeRSS Feed
greg6363
Obsidian | Level 7

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.

5 REPLIES 5
Reeza
Super User

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
Obsidian | Level 7

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
Super User
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
Super User

@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
Super User

@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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1153 views
  • 2 likes
  • 4 in conversation