topic Re: Turn YYYYMMDD date into Year Qtr yyyyQq in SAS Enterprise Guide

Turn YYYYMMDD date into Year Qtr yyyyQq

Cheesiepoof05 — Fri, 20 Jan 2023 21:35:51 GMT

Hello,

I have a date formatted as "Numeric" like YYYYMMDD ie 20220927. I would like to convert that to Year and Quarter such as 2022Q3. I'm using a Proc sql statement to hit against another table (SummaryLookup) to bring back the date and need to add a line to create a second field to convert it to yyyyQq please.

Proc sql;
create table DATE_TEST as
Select

dt_id_YYYYMMDD,

From SummaryLookup;

quit;

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Kurt_Bremser — Fri, 20 Jan 2023 21:42:45 GMT

You only need to change the format of the SAS date variable to YYQ with PROC DATASETS.

Statistical procedures (FREQ, SUMMARY, TABULATE) will use the formatted values to build groups.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Cheesiepoof05 — Fri, 20 Jan 2023 22:02:12 GMT

Thank you but I'm not sure how to put that into my Proc sql; code.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Kurt_Bremser — Fri, 20 Jan 2023 22:07:37 GMT

Please post usable example data (working data steps with datalines) for all datasets used in the join.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

ballardw — Fri, 20 Jan 2023 22:12:14 GMT

@Cheesiepoof05 wrote:

Thank you but I'm not sure how to put that into my Proc sql; code.

You don't have to. Just apply the format when you need it such as in a report or analysis.

What result do you see with this code:

proc freq data= SummaryLookup;
   tables dt_id_YYYYMMDD;
   format dt_id_YYYYMMDD yyq6.;
run;

If your variable is a SAS date value then you should see the values shown in YYYYQN display.

If not, then you may have to create a new variable.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Patrick — Fri, 20 Jan 2023 22:15:37 GMT

Please provide the report created with code as below so we know what we're dealing with (SAS Date value, just a number or a string). Once we know we can go from there.

Proc Contents data=SummaryLookup(keep=dt_id_YYYYMMDD);
run;quit;

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Cheesiepoof05 — Mon, 23 Jan 2023 15:48:44 GMT

This is the code I'm using. It's just hitting against a temporary table where I've already pulled the data from. "adjudication_dt_id" is a "numeric" field with 8 digits. I'm just wanting to create a second field where it takes that numeric field and makes it yyyyQq. So 20220418 in the first field would be 2022Q2 in the second field.

Proc sql;
create table DATE_TEST as
Select
adjudication_dt_id

From SummaryLookup;

quit;

adjudication_dt_id

20190105

20210907

20200430

20221231

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

AMSAS — Mon, 23 Jan 2023 16:24:05 GMT

I'd suggest converting the numeric value into a SAS date value
SAS Date, Time, and Datetime Values
Then you can use SAS date formats

Better still would be to track back where adjudication_dt_id is created and create/read it as a SAS Date value to start with

Here's an example:

data _null_  ;	
	file "test.txt" ;
	infile cards ;
	input ;
	put _infile_ ;
cards ;
20230123
20221205
20220401
;

data readText ;
	infile "test.txt" ;
	input myDate yymmdd8. ;
	put "no format : " myDate  ;
	put "date7.    : " myDate date7. ;
	put "mmyy.     : " myDate mmyy. ;
	put "yyq.      : " myDate yyq. ;
	put "===========================" ;
run ;

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Cheesiepoof05 — Mon, 23 Jan 2023 16:24:39 GMT

I got it figured out. I appreciate everyone's time. I went with:

substr(cat(adjudication_dt_id),1,4) as Year,
substr(cat(adjudication_dt_id),5,2) as Month,
Case
WHEN CALCULATED Month = '01' THEN "Q1"
WHEN CALCULATED Month = '02' THEN "Q1"
WHEN CALCULATED Month = '03' THEN "Q1"
WHEN CALCULATED Month = '04' THEN "Q2"
WHEN CALCULATED Month = '05' THEN "Q2"
WHEN CALCULATED Month = '06' THEN "Q2"
WHEN CALCULATED Month = '07' THEN "Q3"
WHEN CALCULATED Month = '08' THEN "Q3"
WHEN CALCULATED Month = '09' THEN "Q3"
WHEN CALCULATED Month = '10' THEN "Q4"
WHEN CALCULATED Month = '11' THEN "Q4"
WHEN CALCULATED Month = '12' THEN "Q4"
ELSE "ERROR"
END AS Quarter,
catx('',CALCULATED Year, CALCULATED Quarter) AS YEAR_QTR,

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

ballardw — Mon, 23 Jan 2023 17:05:16 GMT

For slightly better, as in "not as long"

Case
WHEN CALCULATED Month in ( '01' '02' '03') THEN "Q1"
WHEN CALCULATED Month in ( '04' '05' '06') THEN "Q2"
WHEN CALCULATED Month in ( '07' '08' '09') THEN "Q3"
WHEN CALCULATED Month in ( '10' '11' '12') THEN "Q4"
ELSE "ERROR"
END AS Quarter,

But in the long run you would be better off turning that not a SAS date value into a SAS date value.

There are multiple functions that allow to do many things with actual dates such as determine intervals between values (how many years/months/quarters/weeks ), see if a date falls on a holiday or which day of the week and many others.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Cheesiepoof05 — Mon, 23 Jan 2023 17:11:56 GMT

Thank you.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

Kurt_Bremser — Mon, 23 Jan 2023 19:01:49 GMT

@Cheesiepoof05 wrote:

I got it figured out. I appreciate everyone's time. I went with:

substr(cat(adjudication_dt_id),1,4) as Year,
substr(cat(adjudication_dt_id),5,2) as Month,
Case
WHEN CALCULATED Month = '01' THEN "Q1"
WHEN CALCULATED Month = '02' THEN "Q1"
WHEN CALCULATED Month = '03' THEN "Q1"
WHEN CALCULATED Month = '04' THEN "Q2"
WHEN CALCULATED Month = '05' THEN "Q2"
WHEN CALCULATED Month = '06' THEN "Q2"
WHEN CALCULATED Month = '07' THEN "Q3"
WHEN CALCULATED Month = '08' THEN "Q3"
WHEN CALCULATED Month = '09' THEN "Q3"
WHEN CALCULATED Month = '10' THEN "Q4"
WHEN CALCULATED Month = '11' THEN "Q4"
WHEN CALCULATED Month = '12' THEN "Q4"
ELSE "ERROR"
END AS Quarter,
catx('',CALCULATED Year, CALCULATED Quarter) AS YEAR_QTR,

This is a very fine (in fact, textbook) example for stupid data causing stupid code.

With a proper date value, this mess of WHENs turns into one simple function call. Depending on the following analysis, this whole step might not be necessary at all, as one can always use the proper format to let statistical procedures create the wanted groups.

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

jebjur — Thu, 26 Jan 2023 21:10:09 GMT

Turning your character date string into a numeric SAS date value with the INPUT function is a much easier option (lot less coding), and then you can apply the YYQw. format to the SAS date value so it displays in the style you want:

data SummaryLookup;
input adjudication_dt_id $;
cards;
20190105
20210907
20200430
20221231
;
run;

proc sql;
create table date_test as select
adjudication_dt_id, input(adjudication_dt_id, yymmdd8.) as new_dt_id format=yyq6.
from SummaryLookup;
quit;

Re: Turn YYYYMMDD date into Year Qtr yyyyQq

SASKiwi — Thu, 26 Jan 2023 21:37:37 GMT

Here is a much better way of doing what you want:

Proc sql;
create table DATE_TEST as
Select
input(adjudication_dt_id, yymmdd8.) as adjudication_dt format = yyq6.
From SummaryLookup;
quit;