topic Re: Date Format for Quarters in SAS Programming

Date Format for Quarters

wendy0327 — Fri, 07 Sep 2018 23:18:46 GMT

Hi community -

would someone be willing to help me create a date format that outputs a sas date of 21185 as "Q12018" rather than the 2018Q1 I get with the YYQ format? I need it to start with the character value because I want to use it as a variable. I cannot find a format like this and I have no experience creating formats. my practice efforts after reading loads of documentation have all been fails.

thanks,

wendy

Re: Date Format for Quarters

Reeza — Sat, 08 Sep 2018 02:48:35 GMT

Post anything you’ve tried and we’d be happy to help. You likely want a PICTURE Format. See this paper, specifically pg 14.

I suppose a link would have helped.

EDIT: http://www2.sas.com/proceedings/sugi31/243-31.pdf

proc format;
    picture QNYYYY (default=12) low-high='%q%Y' (datatype=date);
run;

data have;
    date='01Jan2018'd;
    date_f1=catt('Q', put(qtr(date), 1.), put(year(date), 4.));
    date_f2=put(date, QNYYYY.);
    date_f3=date;
    format date date9. date_f3 QNYYYY.;
run;

Thanks @PGStats

Re: Date Format for Quarters

PGStats — Sat, 08 Sep 2018 02:38:45 GMT

@Reeza No link...

Re: Date Format for Quarters

wendy0327 — Mon, 10 Sep 2018 14:51:47 GMT

Hi -

thank you for your response!

the following code works to put the date into the format I need.

I had attempted this earlier, and kept getting an error I could not resolve.

date_f1=catt('Q', put(qtr(date), 1.), put(year(date), 4.));

now, my issue is to use that process in the following code. "filedate" is a sas date that is on my incoming dataset. the objective is to re-label 7 quarters of wage data (labeled wage1, wage2, etc) with the name of the quarter it represents. the data is for 7 consecutive quarters, so I'm looking to rename 7 variables. the file (and thus the filedate and the variable names) update quarterly.

my code below would work except yyq. (and all the other qtr formats) start with a number value (2018-1) and thus can't be used as a variable name.

I am open to ANY easy workaround.

call symput("wage_1", put(filedate, yyq.));

call symput("wage_2", put(intnx('QTR', filedate, -1), yyq.));

call symput("wage_3", put(intnx('QTR', filedate, -2), yyq.));

call symput("wage_4", put(intnx('QTR', filedate, -3), yyq.));

call symput("wage_5", put(intnx('QTR', filedate, -4), yyq.));

call symput("wage_6", put(intnx('QTR', filedate, -5), yyq.));

call symput("wage_7", put(intnx('QTR', filedate, -6), yyq.));

Re: Date Format for Quarters

Reeza — Mon, 10 Sep 2018 14:59:22 GMT

I'm confused. Your formula will work but you have to apply it to each variable, which is why a SAS date format is more appropriate. My code above illustrates using the custom fomrat. If you replace your format below, YYQ with the format I created it should be what you want. I would also switch that to an array so your code is simpler and it's easier to adjust to more time periods. .

I would also suggest modifying the structure of your data so you don't have to deal with X variables. Dealing with data in a long format is simpler in the long run.

@wendy0327 wrote:

Hi -

thank you for your response!

the following code works to put the date into the format I need.

I had attempted this earlier, and kept getting an error I could not resolve.

date_f1=catt('Q', put(qtr(date), 1.), put(year(date), 4.));

now, my issue is to use that process in the following code. "filedate" is a sas date that is on my incoming dataset. the objective is to re-label 7 quarters of wage data (labeled wage1, wage2, etc) with the name of the quarter it represents. the data is for 7 consecutive quarters, so I'm looking to rename 7 variables. the file (and thus the filedate and the variable names) update quarterly.

my code below would work except yyq. (and all the other qtr formats) start with a number value (2018-1) and thus can't be used as a variable name.

I am open to ANY easy workaround.

call symput("wage_1", put(filedate, yyq.));

call symput("wage_2", put(intnx('QTR', filedate, -1), yyq.));

call symput("wage_3", put(intnx('QTR', filedate, -2), yyq.));

call symput("wage_4", put(intnx('QTR', filedate, -3), yyq.));

call symput("wage_5", put(intnx('QTR', filedate, -4), yyq.));

call symput("wage_6", put(intnx('QTR', filedate, -5), yyq.));

call symput("wage_7", put(intnx('QTR', filedate, -6), yyq.));

Re: Date Format for Quarters

wendy0327 — Mon, 10 Sep 2018 15:23:58 GMT

Thank you again. I got it to work finally.

Reeza, you are dealing with a novice here; I only understood half of what you said. Even so, I'm just so happy to finally get this to work.

thank you thank you thank you.

Re: Date Format for Quarters

Tom — Mon, 10 Sep 2018 16:34:36 GMT

Not sure if you need a format if you just want to generate a string you can use as a variable name. Why not just make a character variable with the string?

length name $32  ;
name = cats('Q', qtr(date), year(date));

Then you could use that variable as the ID variable in PROC transpose to create a variable with that name.

You could make a format using PICTURE format.

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

Re: Date Format for Quarters

wendy0327 — Tue, 11 Sep 2018 12:21:30 GMT

Tom Super User,

thank you! I had done the former, and now I can do the latter. I was not getting my syntax correct for the proc format and still could not get it to work that way. now it works. I appreciate your time.

wendy