topic Re: How to format a data value as QX-YY? in SAS Enterprise Guide

How to format a data value as QX-YY?

MelissaN — Tue, 21 May 2019 20:02:21 GMT

I would like to format a datetime value as QX-YY, for example, Q1-19, Q2-19,...

Any help is much appreciated.

Re: How to format a data value as QX-YY?

PaigeMiller — Tue, 21 May 2019 20:17:02 GMT

As far as I know, there is no format QX-YY.

There are formats YY/X or YY-X or YYQX. So unless you have some very picky and precise requirements, I wouldn't bother trying to get QX-YY. If you absolutely have to have QX-YY, then you probably need to create a new characater variable to match this exact appearance.

Re: How to format a data value as QX-YY?

ballardw — Tue, 21 May 2019 20:18:22 GMT

If year followed by quarter is acceptable, you might consider using the YYQ format.

A custom format can create that though.

Proc format library=work;
picture qyy
low-high ='Q%q-%0y' (datatype=date)
;
run;

data example;
   input date date9.;
datalines;
01Jan1960
23Feb2001
18Dec1901
;
proc print data=example;
   format date qyy.;
run;

Notice that I intentionally picked example dates that might bring the year value into question as I hate two-digit years as almost anyone who worked on Y2K data issues likely does.

You will need to make sure the format is available whenever you need it.

Re: How to format a data value as QX-YY?

Reeza — Tue, 21 May 2019 20:28:41 GMT

How are you planning to use this data? You could take two approaches, create a custom picture format or create a custom format. The custom picture format will not be honoured on graphs, but a custom format will be honoured on graphs.

Re: How to format a data value as QX-YY?

MelissaN — Tue, 21 May 2019 20:30:37 GMT

I am using this data to upload to one of our data source, and then we will use it for creating charts, so I think a custom format is what I am looking for.

Thanks.

Re: How to format a data value as QX-YY?

Reeza — Tue, 21 May 2019 20:37:05 GMT

Creating charts in VA? or a different tool? Custom formats may not work in VA (unsure). If it's a different system entirely (Tableau, PowerBI) its different. The problem with creating a text variable is that it won't sort well. If you had 2018-Q1 that would sort fine, but since alphabetical sort is pretty much the default, you'd have all Q1s then Q2s then Q3s and Q4s order by year within each quarter.

Re: How to format a data value as QX-YY?

Kurt_Bremser — Wed, 22 May 2019 09:42:31 GMT

A format like this is sub-optimal in two ways:

It uses a two-digit year. After the Y2K scare, it should be obvious that 2-digit years are a VERY BAD THING.
It does not sort well on its own. Date/time values should always go from most significant to least significant. Using otherwise structured values will have hilarious results when used in eg graphs (I speak from experience).

So you should go for YYYY-Qx, which is best solved by creating this custom format:

proc format;
picture myquarter
  low-high ='%Y-Q%q' (datatype=date)
;
run;

data test;
x1 = put(today(),myquarter7.);
run;

Re: How to format a data value as QX-YY?

MelissaN — Wed, 22 May 2019 14:26:13 GMT

No, my team members use a different system to create chart. I may check with them to see if they could change the way they format the quarter to make it more efficient.