topic Re: Automating Dates in New SAS User

Automating Dates

MagD — Thu, 19 May 2022 12:56:13 GMT

Good afternoon everyone,

please help me with the automation of my code. I am relatively new to the automation of SAS codes. Please see the code that I am trying to automate below:

data sales;
set Applied_Accounts;

if sales_date <> . then do;
If sales_date le '05Apr2020'd then salesperiod = '00: Sales pre Apr';else
If sales_date le '05May2020'd then salesperiod = '01: 06Apr - 05May2020';else
If sales_date le '05Oct2020'd then salesperiod = '06: 06Sep - 05Oct2020';else
If sales_date le '05Nov2020'd then salesperiod = '07: 06Oct - 05Nov2020';else
If sales_date le '01Dec2020'd then salesperiod = '08: 06Nov - 01Dec2020';else
If sales_date le '05Jan2021'd then salesperiod = '09: 02Dec - 05Jan2021';else
If sales_date le '05Feb2021'd then salesperiod = '10: 06Jan - 05Feb2021';else
If sales_date le '05Mar2021'd then salesperiod = '11: 06Feb - 05Mar2021';else
If sales_date le '31Mar2021'd then salesperiod = '12: 06Mar - 31Mar2021';else
If sales_date le '30Apr2021'd then salesperiod = '13: 01Apr - 30Apr2021';else
If sales_date le '31May2021'd then salesperiod = '14: 01May - 31May2021';else
If sales_date le '30Jun2021'd then salesperiod = '15: 01Jun - 30Jun2021';else
If sales_date le '31Jul2021'd then salesperiod = '16: 01Jul - 31Jul2021';else
If sales_date le '31Aug2021'd then salesperiod = '17: 01Aug - 31Aug2021';else
If sales_date le '30Sep2021'd then salesperiod = '18: 01Sep - 30Sep2021';else
If sales_date le '31Oct2021'd then salesperiod = '19: 01Oct - 31Oct2021';else
If sales_date le '30Nov2021'd then salesperiod = '20: 01Nov - 30Nov2021';else
If sales_date le '31Dec2021'd then salesperiod = '21: 01Dec - 31Dec2021';else
If sales_date le '31Jan2022'd then salesperiod = '22: 01Jan - 31Jan2022';else
If sales_date le '28Feb2022'd then salesperiod = '23: 01Feb - 28Feb2022';else
If sales_date le '31Mar2022'd then salesperiod = '24: 01Mar - 31Mar2022';else
If sales_date le '30Apr2022'd then salesperiod = '25: 01Apr - 30Apr2022';else
If sales_date le '31May2022'd then salesperiod = '26: 01May - 31May2022';else

salesperiod = '999:Update code';
end;

run;

I want to automate this code so that I do not have to manually input the sales_data and the salesperiod.

Thank you in advance for your response.

Re: Automating Dates

AMSAS — Thu, 19 May 2022 12:59:18 GMT

I'd suggest you look at custom formats:

PROC FORMAT

Specifically the CNTLIN/CNTLOUT

Re: Automating Dates

Tom — Thu, 19 May 2022 13:07:35 GMT

I don't understand what you want to automate.

The code you showed means that you do not have to manually enter any dates, the dates are coming from the input dataset. You also do not need to manually enter any periods since the IF/THEN series will assign those based on the dates.

It might be easier to use a format instead.

If there is some RULE you can explain that generates the cutoff dates for the periods then you could make some code to generate a format. But I do not see any clear pattern to the cutoff dates.

Re: Automating Dates

MagD — Thu, 19 May 2022 13:14:01 GMT

Hey Tom,

So I want to automate the code so that I do not need to keep manually adding more dates to the code, i.e. I won't have to manually add next month's date which will be June, i.e
if sales_date le '30Jun2022'd then salesperiod = '27:' 01Jun - 30Jun2022';

Re: Automating Dates

PaigeMiller — Thu, 19 May 2022 13:30:46 GMT

But we can't help you automate this until you provide explanation of the pattern.

Why do some time periods begin on the 1st day of the month?

Why do other time periods begin on the 5th day of the month?
What is the logic here? How can we know what day of the month is the beginning of the time period?

Re: Automating Dates

MagD — Thu, 19 May 2022 13:41:56 GMT

Hi Paige,

Apologies for not providing a lot of information.

The beginning of each month is the 1st, so next month's date will be 01Jun - 30Jun2022.

Some time periods started at the 5th day of the month but that has been changed to the 1st day of the month hence the differences but the beginning of the time period is the 1st- I hope this helps.

Re: Automating Dates

PaigeMiller — Thu, 19 May 2022 13:48:51 GMT

Some time periods started at the 5th day of the month but that has been changed to the 1st day of the month hence the differences but the beginning of the time period is the 1st- I hope this helps.

Does this mean that all time periods, even the ones that start you show starting at the 5th day of the month in your table, are really starting on the 1st day of the month? Or does it mean something else?

If all months begin on the first day of the month, you can use a format to produce a month name such as APR2022, or you can use a custom format from PROC FCMP if it absolutely has to be shown as 01APR2022–31APR2022. But that's extra work that (in my opinion) provides no extra value, as APR2022 is clear to everyone.

Your value of '25: 01Apr - 30Apr2022' also seems to be extra work for no benefit, why is the 25 necessary?

Re: Automating Dates

acordes — Thu, 19 May 2022 13:56:56 GMT

For me that makes no sense. It's a better idea to convince your peers that native formats in SAS should be used.

data have;
infile datalines;
input fecha;
format fecha ddmmyy10.;
informat fecha date9.;
datalines;
05Apr2020
05May2020
05Oct2020
05Nov2020
01Dec2020
05Jan2021
05Feb2021
05Mar2021
31Mar2021
30Apr2021
31May2021
30Jun2021
31Jul2021
31Aug2021
30Sep2021
31Oct2021
30Nov2021
31Dec2021
31Jan2022
28Feb2022
31Mar2022
30Apr2022
31May2022
;
run;

data step;
set have ;
format start stop date9.;
start=lag(fecha)+1;
stop=fecha;
run;

proc sql;
select cats (quote(put(start, date9.), "'"), "d-", quote(put(stop, date9.), "'"), 'd=', 
"'"||put(intck('month', '05apr2020'd, start)+1, z2.), catx(" ", ":", substr(put(start, date9.),1,5), "-", put(stop, date9.))||"'") into :oks separated by " "
from step;
quit;

PROC FORMAT;

VALUE dtfmt
low -       '05apr2020'd='00: Sales pre Apr'    
'06APR2020'd-'05MAY2020'd='01: 06APR - 05MAY2020'
'06MAY2020'd-'05OCT2020'd='02: 06MAY - 05OCT2020'
'06OCT2020'd-'05NOV2020'd='07: 06OCT - 05NOV2020'
'06NOV2020'd-'01DEC2020'd='08: 06NOV - 01DEC2020'
'02DEC2020'd-'05JAN2021'd='09: 02DEC - 05JAN2021'
'06JAN2021'd-'05FEB2021'd='10: 06JAN - 05FEB2021'
'06FEB2021'd-'05MAR2021'd='11: 06FEB - 05MAR2021'
'06MAR2021'd-'31MAR2021'd='12: 06MAR - 31MAR2021'
'01APR2021'd-'30APR2021'd='13: 01APR - 30APR2021'
'01MAY2021'd-'31MAY2021'd='14: 01MAY - 31MAY2021'
'01JUN2021'd-'30JUN2021'd='15: 01JUN - 30JUN2021'
'01JUL2021'd-'31JUL2021'd='16: 01JUL - 31JUL2021'
'01AUG2021'd-'31AUG2021'd='17: 01AUG - 31AUG2021'
'01SEP2021'd-'30SEP2021'd='18: 01SEP - 30SEP2021'
'01OCT2021'd-'31OCT2021'd='19: 01OCT - 31OCT2021'
'01NOV2021'd-'30NOV2021'd='20: 01NOV - 30NOV2021'
'01DEC2021'd-'31DEC2021'd='21: 01DEC - 31DEC2021'
'01JAN2022'd-'31JAN2022'd='22: 01JAN - 31JAN2022'
'01FEB2022'd-'28FEB2022'd='23: 01FEB - 28FEB2022'
'01MAR2022'd-'31MAR2022'd='24: 01MAR - 31MAR2022'
'01APR2022'd-'30APR2022'd='25: 01APR - 30APR2022'
'01MAY2022'd-'31MAY2022'd='26: 01MAY - 31MAY2022'
other = '999:Update code';

RUN;

data fake;
format f_date ddmmyy10.;
do i=1 to 1000;
f_date=rand("integer", '01jan2020'd, today());
output;
end;
run;

proc means data=fake n;
format f_date dtfmt.;
class f_date;
var i;
run;

Re: Automating Dates

Kurt_Bremser — Thu, 19 May 2022 14:32:48 GMT

Let's start with a few cornerstones:

is the switch from the 6th to the 1st fixed on April 2021?
is period 1 always April 2020?

Re: Automating Dates

Tom — Thu, 19 May 2022 15:46:50 GMT

That first IF statement if very fishy looking.

if sales_date <> . then do;

Why are you using the MAX operator?

Since the right hand value is missing then the result is just going to be the left hand value.

So it is the same as doing:

if sales_date then do;

Which will be true when ever SALES_DATE is not missing and not 01JAN1960.

Re: Automating Dates

Reeza — Thu, 19 May 2022 16:01:11 GMT

Create a format that's dynamic, you can set how much it grows and use that instead. You could just set the end of the loop to a large number and use that.

data date_fmt;
fmtname = 'DATERANGE_FMT';
type = 'N';

length label $50.;
START=.; END='05APR2020'd; label = '00: Sales pre Apr'; HLO="L"; output; 
call missing(HLO);
START= '06APR2020'd; END='05MAY2020'd; label = '01: 06Apr - 05May2020'; output;

/*****type out the rest of your ranges manually*****/

*everything after April is automated;
do i=13 to 30;
START=intnx('month', '01APR2020'd, i-1 , 'b');
END = intnx('month', '01APR2020'd, i-1, 'e');
label = cat(i, ": ", substr(put(start, date9.), 1, 5), " - ", put(end, date9.));
output;
end;

run;

proc format cntlin=date_fmt;
run;


data applied_accounts;
input sales_date date9.;
format sales_date date9.;
cards;
03APR2020
08APR2020
30APR2021
14MAR2022
;;;;
run;


data sales;
set applied_accounts;

salesperiod = put(sales_date, daterange_fmt.);
run;

Re: Automating Dates

mkeintz — Fri, 20 May 2022 03:12:54 GMT

@MagD wrote (emphasis mine)

Some time periods started at the 5th day of the month but that has been changed to the 1st day of the month

You are saying you have an irregular historic set of date ranges up through the 06MAR2021-31MAR2021, and subsequently regular whole month date ranges. So you could just use a logical structure such as this pseudo-code:

data sales;
  set Applied_Accounts;
  if not missing(sales_date) then do;
    if sales_date <='31mar2021'd then salesperiod = put(sales_date,dtfmt.); else
    salesperiod=function-of-sales_date-distance-from-31mar2021;
  end;
run;

This assumes you use part of the format DTFMT shown by @acordes. Something like

proc format ;
  VALUE dtfmt
    low         - '05apr2020'd='00: Sales pre Apr'    
   '06APR2020'd- '05MAY2020'd='01: 06APR - 05MAY2020'
   '06MAY2020'd-'05OCT2020'd='02: 06MAY - 05OCT2020'
   '06OCT2020'd-'05NOV2020'd='07: 06OCT - 05NOV2020'
   '06NOV2020'd-'01DEC2020'd='08: 06NOV - 01DEC2020'
   '02DEC2020'd-'05JAN2021'd='09: 02DEC - 05JAN2021'
   '06JAN2021'd-'05FEB2021'd='10: 06JAN - 05FEB2021'
   '06FEB2021'd-'05MAR2021'd='11: 06FEB - 05MAR2021'
   '06MAR2021'd-'31MAR2021'd='12: 06MAR - 31MAR2021'
   other='xxxxxx';
run;

data sales;
  set Applied_Accounts;
  if not missing(sales_date) then do;
    if sales_date <= '31mar2021'd then salesperiod=put(sales_date,dtfmt.); else
    salesperiod= catx(' '
                     ,cats(intck('month','31mar2021'd,sales_date)+12,': 01',put(sales_date,monname3.))
                     ,'-'
                     ,put(intnx('month',sales_date,0,'e'),date9.)
                     );
    end;
  end;
run;

Since the range 06mar2021 through 31mar2021 is range number 12, then every date afterwards is just 12 plus the number of months the sales_date follows 31mar2021 - hence the "intck(.......) + 12" is the first argument of the CATS function.

Re: Automating Dates

MagD — Fri, 20 May 2022 09:20:27 GMT

Thank you so much for this solution - it works perfectly.

Thank you for your assistance. I appreciate it.

Re: Automating Dates

MagD — Fri, 20 May 2022 09:22:01 GMT

Thank you so much for this approach - I tested it out and it also works perfectly as well.

Thank you for taking the time to assist me.

Re: Automating Dates

MagD — Fri, 20 May 2022 09:23:13 GMT

Thank you so much for your response and recommended approach. I truly appreciate it.

Re: Automating Dates

PaigeMiller — Fri, 20 May 2022 10:00:05 GMT

The problem with the solution from @mkeintz is that it turns dates into character variables, which sort alphabetically, and so then you have to resort to adding in a sequence number at the start to force it to sort chronologically. The '25: ' or similar at the start is meaningless; no one knows what the 25 means but it takes up space and it confuses people.

If you use custom formats via PROC FCMP, or even the simple MONYY. format, then the values can be sorted chronologically, and thus it is not only simpler, but easier for humans to read.

Re: Automating Dates

mkeintz — Fri, 20 May 2022 22:05:16 GMT

@PaigeMiller wrote:

The problem with the solution from @mkeintz is that it turns dates into character variables, which sort alphabetically, and so then you have to resort to adding in a sequence number at the start to force it to sort chronologically. The '25: ' or similar at the start is meaningless; no one knows what the 25 means but it takes up space and it confuses people.

If you use custom formats via PROC FCMP, or even the simple MONYY. format, then the values can be sorted chronologically, and thus it is not only simpler, but easier for humans to read.

@PaigeMiller 's criticism of the text variable shortcomings is right of course, but in this case not until July 2028, when the alphabetic sort will fail to replicate chronological order..

Until then, I can hide behind the OP's literal request, because each salesperiod text value starts with a two-digit (z2. format) enumerated date range (01 for 06apr2020-05may2020, etc.). This will replicate date order until range number 100 occurs - (July 2028 I think) when three digits will be needed.

I guess one could extend my solution by using Z3. format (and modifying accordingly the VALUE statement for the irregular historic date ranges). That would provide another 900 months (July 2104). So there can be wiggle room.