BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have a date field in a table that has the format DDMMYY10. In a data step I want to change the format so that I can group the data by Year/Quarter in a Proc Tabulate.

I have this code in a data step:
FORMAT datefield DTYYQC6.;

however while it produces the correct format it treats every value as if it were 0 (i.e. it returns 1960:1 no matter what value it reads from the source table). If I change the format to something else, i.e. MONYY7. it works OK. Any ideas why the DTYYQC format is having this effect?

I need to apply this format to many date fields in the source table so I don't really want to extract & concatenate using the YEAR() and QTR() functions for each field which would be an alternative if this problem were limited to one field.

Thanks
Rob
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
Hi:
I suspect you have a date field, NOT as date/time field (based on the fact that the MONYY format works. The DTYYQC format is meant to select out the date part of a date/time field (as if your data were representing this: 15Nov1950:07:35:24) but you might want to check out the use of the regular YYQ formats and not the DTYYQC format. If you scroll to the bottom of the Format list where you found the DT version, you'll find the YYQ formats.

cynthia
deleted_user
Not applicable
A restatement of what Cynthia wrote, but expressed differently.

SAS stores numbers or characters.
Dates are simply/essentially formatted numbers.
Times are simply/essentially formatted numbers.

A Date format assumes 1 = 1 day, and 0 = January 1, 1960
A Time format assumes 1 = 1 second, and 0 = 00:00:00
A DateTime format assumes 1 = 1 second and 0 = 00:00:00 on January 1, 1960

Your data was apparently created to represent a date, not a datetime value.
The format you are using is a datetime format, that is what the "DT" prefix to the format name means.

Today = 17,657 days after January 1, 1960
Right now = 39,977 seconds after midnight (00:00:00)
Right now = 1525604777 seconds after 00:00:00 on January 1, 1960

So, today expressed as a datetime value would be 01JAN1960:04:54:17
deleted_user
Not applicable
Hi

YYQC6. worked fine.

Thanks
Rob

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 1466 views
  • 0 likes
  • 2 in conversation