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