SAS EG coding for date in character format and automating process for previous quarters

btwovegas — Wed, 24 Aug 2022 17:47:06 GMT

I'm usually utilizing SAS EG for simple automated pulls and using the PROMPT option to choose to go back so many quarters or months. However since the database I am working with right now utilizes a Character format, the SAS system does not properly recognize it as a date and therefore I can not utilize the date format ( example 20220312 instead of 12Mar2022). So i have to utilize a CASE WHEN SUBSTR option to break down the character date into quarters that fall between the WHERE option dates.

Is there any method in SAS EG that you can tell the code to search for the last 5 quarters of data automatically without having to input dates in it and changing the dates every quarter? It works as is, but I prefer to just automate it as this code below is in several dozen other EGP's and would hate to miss a replacement next quarter.

Here are the parts to the code which I have to utilize every quarter;

CASE WHEN SUBSTR(WLM.ACQ_DTE, 1, 6) BETWEEN '202204' AND '202206' THEN '2022Q2'

WHEN SUBSTR(WLM.ACQ_DTE, 1, 6) BETWEEN '202201' AND '202203' THEN '2022Q1'

WHEN SUBSTR(WLM.ACQ_DTE, 1, 6) BETWEEN '202110' AND '202112' THEN '2021Q4'

WHEN SUBSTR(WLM.ACQ_DTE, 1, 6) BETWEEN '202107' AND '202109' THEN '2021Q3'

WHEN SUBSTR(WLM.ACQ_DTE, 1, 6) BETWEEN '202104' AND '202106' THEN '2021Q2'

ELSE 'OTHER' END AS ACQ_Q,

WHERE SUBSTR(WLM.ACQ_DTE, 1, 6) BETWEEN '202104' AND '202206'

Re: SAS EG coding for date in character format and automating process for previous quarters

ErikLund_Jensen — Wed, 24 Aug 2022 20:28:15 GMT

Hi @btwovegas

You have a database with dates as character strings in the format yyyymmdd, and you want all records where the date is within the last 5 quarters. This can be done in a simple query as shown in the following code.

The principle is that the text in variable ACQ_DTE is converted to a SAS date, which should be >= tha date that marks the beginning of the 4.complete quarter before the current date, and the current quarter is counted as the 5.

If you want 5 full quarters + the current quarter, then specify -5 instead of -4 to the intnx function.

data have;
  ACQ_DTE = '20220812'; output;
  ACQ_DTE = '20220412'; output;
  ACQ_DTE = '20220312'; output;
  ACQ_DTE = '20211212'; output;
  ACQ_DTE = '20211012'; output;
  ACQ_DTE = '20210812'; output;
  ACQ_DTE = '20210512'; output;  
  ACQ_DTE = '20210112'; output;
run;

proc sql;
  create table want as
    select 
      ACQ_DTE,
      put(input(ACQ_DTE,yymmdd8.),yyq6.) as ACQ_Q
    from have
    where input(ACQ_DTE,yymmdd8.) >= intnx('quarter',date(),-4,'begin');
quit;

topic SAS EG coding for date in character format and automating process for previous quarters in SAS Programming

SAS EG coding for date in character format and automating process for previous quarters

Re: SAS EG coding for date in character format and automating process for previous quarters