Automation of Dates

[MagD]

Good morning everyone,

I am very new to automating of codes/dates - Please help me with automating dates in my piece of code. I currently have the following coded up:

data test;
set testing;

if '05Apr2019'd < csa_inv_dte < '06May2019'd  then trademonth = '01:APR 2019'; else
if '05May2019'd < csa_inv_dte < '06Jun2019'd  then trademonth = '02:MAY 2019'; else
if '05jun2019'd < csa_inv_dte < '06Jul2019'd     then trademonth = '03:JUN 2019'; else
if '05jul2019'd < csa_inv_dte < '06Aug2019'd     then trademonth = '04:JUL 2019'; else
if '05aug2019'd < csa_inv_dte < '06Sep2019'd  then trademonth = '05:AUG 2019'; else
if '05sep2019'd < csa_inv_dte < '06Oct2019'd   then trademonth = '06:SEP 2019'; else
if '05Oct2019'd < csa_inv_dte < '06Nov2019'd   then trademonth = '07:OCT 2019'; else
if '05Nov2019'd < csa_inv_dte < '02Dec2019'd  then trademonth = '08:NOV 2019'; else
if '01Dec2019'd < csa_inv_dte < '06Jan2020'd  then trademonth = '09:DEC 2019'; else
if '05Jan2020'd < csa_inv_dte < '06Feb2020'd  then trademonth = '10:JAN 2020'; else
if '05Feb2020'd < csa_inv_dte < '06Mar2020'd  then trademonth = '11:FEB 2020'; else
if '05Mar2020'd < csa_inv_dte < '06Apr2020'd  then trademonth = '12:MAR 2020'; else

if '05Apr2020'd < csa_inv_dte < '06May2020'd  then trademonth = '13:APR 2020'; else
if '05May2020'd < csa_inv_dte < '06Jun2020'd  then trademonth = '14:MAY 2020'; else
if '05jun2020'd < csa_inv_dte < '06Jul2020'd     then trademonth = '15:JUN 2020'; else
if '05jul2020'd < csa_inv_dte < '06Aug2020'd    then trademonth = '16:JUL 2020'; else
if '05aug2020'd < csa_inv_dte < '06Sep2020'd  then trademonth = '17:AUG 2020'; else
if '05sep2020'd < csa_inv_dte < '06Oct2020'd   then trademonth = '18:SEP 2020'; else
if '05oct2020'd < csa_inv_dte < '06Nov2020'd   then trademonth = '19:OCT 2020'; else
if '05nov2020'd < csa_inv_dte < '02Dec2020'd  then trademonth = '10:NOV 2020'; else
if '01Dec2020'd < csa_inv_dte < '06Jan2021'd  then trademonth = '21:DEC 2020'; else
if '05Jan2021'd < csa_inv_dte < '06Feb2021'd  then trademonth = '22:JAN 2021'; else
if '05Feb2021'd < csa_inv_dte < '06Mar2021'd  then trademonth = '23:FEB 2021'; else
if '05Mar2021'd < csa_inv_dte < '01Apr2021'd  then trademonth = '24:MAR 2021'; else

if '31Mar2021'd < csa_inv_dte < '01May2021'd then trademonth = '25:APR 2021'; else
if '30Apr2021'd < csa_inv_dte < '01Jun2021'd   then trademonth = '26:MAY 2021'; else
if '31May2021'd < csa_inv_dte < '01Jul2021'd   then trademonth = '27:JUN 2021'; else
if '30Jun2021'd < csa_inv_dte < '01Aug2021'd  then trademonth = '28:JUL 2021'; else
if '31Jul2021'd < csa_inv_dte < '01Sep2021'd   then trademonth = '29:AUG 2021'; else
if '31Aug2021'd < csa_inv_dte < '01Oct2021'd  then trademonth = '30:SEP 2021'; else
if '30Sep2021'd < csa_inv_dte < '01Nov2021'd  then trademonth = '31:OCT 2021'; else
if '31Oct2021'd < csa_inv_dte < '01Dec2021'd  then trademonth = '32:NOV 2021'; else
if '30Nov2021'd < csa_inv_dte < '01Jan2022'd  then trademonth = '33:DEC 2021'; else
if '31Dec2021'd < csa_inv_dte < '01Feb2022'd  then trademonth = '34:JAN 2022'; else
if '31Jan2022'd < csa_inv_dte < '01Mar2022'd  then trademonth = '35:FEB 2022'; else
if '28Feb2022'd < csa_inv_dte < '01Apr2022'd  then trademonth = '36:MAR 2022'; else

if csa_inv_dte = .                                                then trademonth = '99: No Inv';
format trademonth $30.;

run;

I need to add more dates every month to create the trademonth variable but I want to automate this process to automatically add new month's dates. Please assist me with this process.

[Patrick]

This can be done for sure but you need to explain to us the rule that needs to apply.

Normally we can derive the rule from code shared but.... it looks to me that there are some issues with your logic.

Below dates are overlapping. Because you don' have an ELSE statement the 2nd IF statement will come into effect. Is that what's required?

Most of the time the beginning of a date boundary appears to be the 5th calendar day of a month. But then there are cases which are different.

You need to explain to us the logic for how the boundaries get defined as else it's not possible to express such logic in code.

[MagD]

Hi Patrick,

Thank you for responding to my query.

Yes, Because I don't have an ELSE statement the 2nd IF statement will come into effect - that's what is required.

I think we can focus on this part of the code for now just to keep consistency:

if '31Mar2021'd < csa_inv_dte < '01May2021'd then trademonth = '25:APR 2021'; else
if '30Apr2021'd < csa_inv_dte < '01Jun2021'd then trademonth = '26:MAY 2021'; else
if '31May2021'd < csa_inv_dte < '01Jul2021'd then trademonth = '27:JUN 2021'; else
if '30Jun2021'd < csa_inv_dte < '01Aug2021'd then trademonth = '28:JUL 2021'; else
if '31Jul2021'd < csa_inv_dte < '01Sep2021'd then trademonth = '29:AUG 2021'; else
if '31Aug2021'd < csa_inv_dte < '01Oct2021'd then trademonth = '30:SEP 2021'; else
if '30Sep2021'd < csa_inv_dte < '01Nov2021'd then trademonth = '31:OCT 2021'; else
if '31Oct2021'd < csa_inv_dte < '01Dec2021'd then trademonth = '32:NOV 2021'; else
if '30Nov2021'd < csa_inv_dte < '01Jan2022'd then trademonth = '33:DEC 2021'; else
if '31Dec2021'd < csa_inv_dte < '01Feb2022'd then trademonth = '34:JAN 2022'; else
if '31Jan2022'd < csa_inv_dte < '01Mar2022'd then trademonth = '35:FEB 2022'; else
if '28Feb2022'd < csa_inv_dte < '01Apr2022'd then trademonth = '36:MAR 2022'; else

if csa_inv_dte = . then trademonth = '99: No Inv';

So next month's code will include: if '31Mar2022'd < csa_inv_dte < '01May2022'd then trademonth = '37:APR 2022'; else
but I want to automate these dates.

Please let me know if I am being clear or not.

[Kurt_Bremser]

Your code screams for using a formula.

You want a string consisting of a running count of months (starting at a given point in time), followed by a colon and the monthname+year, separated by a blank.

See this formula:

%let start = %sysevalf("31mar2019"d);


trademonth = 
  put(intck("month",&start.,csa_inv_dte),2.) !! 
  ":" !!
  upcase(put(csa_inv_dte,monname3.)) !!
  " " !! 
  put(csa_inv_dte,year4.)
;

Applied to some fake data:

%let start = %sysevalf("31mar2019"d);

data want;
input csa_inv_dte :yymmdd10.;
format csa_inv_dte yymmdd10.;
trademonth = 
  put(intck("month",&start.,csa_inv_dte),2.) !! 
  ":" !!
  upcase(put(csa_inv_dte,monname3.)) !!
  " " !! 
  put(csa_inv_dte,year4.)
;
datalines;
2020-03-05
2022-04-06
;