BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Patrick
Opal | Level 21

@Timbim

And now applying @Tom's logic to your code things could look like:

%let old = %sysfunc(intnx(weekday17w,%sysfunc(today()),-2));
%let new = %sysfunc(intnx(weekday2w,%sysfunc(today()),-1)) ;
proc sql;
  create table daily_t as
    select 
      date,
      case
        when date = &old then 'previous' 
        else 'current'
      end
      as period,
      accno,
      branchnumber,
      accbal
    from account_daily_table
      where date in (&old, &new);
quit;

You can of course also use the intnx() functions directly within your SQL in place of the macro variables. 

Timbim
Obsidian | Level 7

Hi Patrick,

 

I am using the below as you've stated and it worked perfectly when I ran the code yesterday (Monday) where it gave me the old date = Thursday and new date = Saturday however it is not working when I ran today (Tuesday). It gave me old date = Friday and new date = Sunday when it should be old date = Saturday and new date = Mon

 

 

%let old = %sysfunc(intnx(weekday17w,%sysfunc(today()),-2));
%let new = %sysfunc(intnx(weekday2w,%sysfunc(today()),-1)) ;
proc sql;
  create table daily_t as
    select
      date,
      case
        when date = &old then 'previous'
        else 'current' end as period,
      accno,
      branchnumber,
      accbal
    from account_daily_table where date in (&old, &new);
quit;

 

Appreciate your help.

 

Thanks kindly,

Mags

Patrick
Opal | Level 21

@Timbim

I've missed that the example @Tom posted used the first (and wrong) logic to determine the old and new dates.

Still following what @Tom posted but amending to the final desired result below should do the job.

 

The first data step is just re-implementing Tom's sample code with the amended logic, the second data step is how you could implement this. 


I've chosen this time a data NULL step to create the macro variables to avoid this sometimes hard-to-read spaghetti code when using %sysfunc().

data test ;
  do today = '16APR2018'd to '20APR2018'd ;
    new = intnx('weekday1w',today,-1) ;
    old = intnx('weekday1w',intnx('weekday17w',today,-1),-1) ;
    day1 = put(today,downame.-l);
    day2 = put(old,downame.-l);
    day3 = put(new,downame.-l);
    output;
    format today old new yymmdd10. ;
  end;
run;
proc print; run;


data _null_;
  format 
    executionDate previousExecutiondate
    startDate endDate WEEKDATX.;

/*  executionDate='17APR2018'd;*/
  executionDate         =today();
  previousExecutiondate =intnx('weekday17w',executionDate,-1);

  startDate =intnx('weekday1w',previousExecutiondate,-1);
  endDate   =intnx('weekday1w',executionDate,-1);

  put _all_;

  call symputx('old',put(startDate,10.));
  call symputx('new',put(endDate,10.));
  stop;
run;
Timbim
Obsidian | Level 7

Hi Patrick,

 

Thanks for your response. How do I use your logic for the following? I don't have indepth knowledge in SAS but aiming to get to know SAS a lot better. Your help will solve a 4 week project that I've been working on.

 

Your logic:

 

data test;

format

 

executionDate previousExecutiondate

startDate endDate WEEKDATX.;

 

/* executionDate='17APR2018'd;*/

executionDate =today();

previousExecutiondate =intnx('weekday17w',executionDate,-1);

startDate =intnx('weekday1w',previousExecutiondate,-1);

endDate =intnx('weekday1w',executionDate,-1);

put _all_;

call symputx('old',put(startDate,10.));

call symputx('new',put(endDate,10.));

stop;

run;

 

 

My code:

 

* Date Comparisons;

%let old = '17Apr2018'd; ;*old date;

%let new = '18Apr2018'd; ;*new date

 

%_eg_conditional_dropds(WORK.DAILY_T);

PROC SQL;

CREATE TABLE WORK.DAILY_T AS SELECT

   Date,

   AccNo,

   BranchNumber,

   AccBal

FROM ACCOUNT_DAILY_TABLE

WHERE Date IN (&old, &new);

RUN;

 

Thanks kindly,

Mags

 

Patrick
Opal | Level 21

Hi @Timbim (Mags),

 

You simply need some sort of code which populates two SAS macro variables with the correct SAS data values. There are very often different coding options available. The code I've posted (data _null_ step) does nothing else than creating and populating the SAS macro variables &old and &new and though the code would look like:

data _null_;
  format
    executionDate previousExecutiondate
    startDate endDate WEEKDATX.;
  executionDate =today();
  previousExecutiondate =intnx('weekday17w',executionDate,-1);
  startDate =intnx('weekday1w',previousExecutiondate,-1);
  endDate =intnx('weekday1w',executionDate,-1);
  /*  put _all_;*/
  call symputx('old',put(startDate,10.));
  call symputx('new',put(endDate,10.));
  stop;
run;
 
proc sql;
  create table work.daily_t as select
     date,
     accno,
     branchnumber,
     accbal
  from account_daily_table
  where date in (&old, &new);
run;

Another coding option is to use %sysfunc() which allows for usage of SAS Base functions on SAS Macro level. I like this coding option BUT if one needs more than one SAS function to get to the desired result then the code becomes quickly very hard to understand and maintain. That's why I don't recommend to use below alternative here. It's just for demonstration.

%let old=%sysfunc(intnx(weekday1w,%sysfunc(intnx(weekday17w,%sysfunc(today()),-1)),-1));
%let new=%sysfunc(intnx(weekday1w,%sysfunc(today()),-1));
 
proc sql;
  create table work.daily_t as select
     date,
     accno,
     branchnumber,
     accbal
  from account_daily_table
  where date in (&old, &new);
run;

 

Timbim
Obsidian | Level 7

 Hi Patrick,

 

Thank you so so much. I've just tested  your first code and it is working now for execution day Wednesday. I'll test for execution Day Monday and Tuesday and post a response.

 

data _null_;

format

 

executionDate previousExecutiondate

startDate endDate WEEKDATX.;

 

executionDate =today();

previousExecutiondate =intnx('weekday17w',executionDate,-1);

startDate =intnx('weekday1w',previousExecutiondate,-1);

endDate =intnx('weekday1w',executionDate,-1);

call symputx('old',put(startDate,10.));

call symputx('new',put(endDate,10.));

stop;

run;

 

 

Thank you for being very helpful.

 

Kind regards,

Mags

Timbim
Obsidian | Level 7

Hi Patrick,

 

You are amazing and thank you. Your code worked perfectly and I was able to complete my project successfully.

 

 

Thanks heaps!

Patrick
Opal | Level 21

@Kurt_Bremser

Thank you. 

The SAS calendar functions like intnx() and intck() are indeed very powerful and once you get the hang of it allow very often for simple coding solutions to "shifting dates" problems which feel at first hard to solve.

Timbim
Obsidian | Level 7
@ Patrick you’ve understood correctly. The execution, start and end dates are correct on your table. Yes, we skip Friday. The tables have account information and we get a snapshot of Saturday’s end of day balance which already includes Friday.

Thanks kindly,
Mags
Kurt_Bremser
Super User

@Timbim wrote:

Hi Team,

 

I am working on automating some of the manual steps in a new team and in need of your SAS knowledge.

 

There is this particular date logic within SAS that my new team keeps changing every day.

 

Whilst I know how to determine the previous day and today's date, there is this other additional weekly change that I don't know how to do, hence I am here.

My new team have to change the reporting date as per follows:

 

1. Every Monday

   'old date'  must be Thursday of last week and 'new date' must be Saturday of last week

 

2. Every Tuesday

   'old date' must be Friday of last week and 'new date' must be Sunday of last week.

 

3. Every other day

 'old date' = previous day

  'new date' = today () minus 1

 

would there be a function that could do all  1, 2 & 3 ?

 

Truly appreciate your help,

 

Mags



This code delivers exactly what you want:

data _null_;
format date old_date new_date weekdate30.;
do date = today() - 7 to today();
  if weekday(date) in (2,3)
  then do;
    old_date = date - 4;
    new_date = date - 2;
  end;
  else do;
    old_date = date - 1;
    new_date = today() - 1;
  end;
  put date=;
  put old_date=;
  put new_date=;
end;
run;

Log:

 

27         data _null_;
28         format date old_date new_date weekdate30.;
29         do date = today() - 7 to today();
30           if weekday(date) in (2,3)
31           then do;
32             old_date = date - 4;
33             new_date = date - 2;
34           end;
35           else do;
36             old_date = date - 1;
37             new_date = today() - 1;
38           end;
39           put date=;
40           put old_date=;
41           put new_date=;
42         end;
43         run;

date=Tuesday, April 17, 2018
old_date=Friday, April 13, 2018
new_date=Sunday, April 15, 2018
date=Wednesday, April 18, 2018
old_date=Tuesday, April 17, 2018
new_date=Monday, April 23, 2018
date=Thursday, April 19, 2018
old_date=Wednesday, April 18, 2018
new_date=Monday, April 23, 2018
date=Friday, April 20, 2018
old_date=Thursday, April 19, 2018
new_date=Monday, April 23, 2018
date=Saturday, April 21, 2018
old_date=Friday, April 20, 2018
new_date=Monday, April 23, 2018
date=Sunday, April 22, 2018
old_date=Saturday, April 21, 2018
new_date=Monday, April 23, 2018
date=Monday, April 23, 2018
old_date=Thursday, April 19, 2018
new_date=Saturday, April 21, 2018
date=Tuesday, April 24, 2018
old_date=Friday, April 20, 2018
new_date=Sunday, April 22, 2018
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

although it esacpes me why you want today()-1 as new_date  for all weekdays except Monday&Tuesday. Shouldn't that also be derived from date?

 

Timbim
Obsidian | Level 7

Hi Kurt,

 

Every Tuesday statement made in the first posting is incorrect. It should be as per below:

 

Execution Datestart dateend date
MondayThursdaySaturday
TuesdaySaturdayMonday
WednesdayMondayTuesday
ThursdayTuesdayWednesday
FridayWednesdayThursday

 

Below is what I need when I run the code on specific days :

 Every Mondays, I want it to return me Thursday's and Saturday's  records

Every Tuesdays, I want it to return me Saturday's and Monday's  records

Every Wednesday, I want it to return Monday's and Tuesday's records

Every Thursday, I want it to return Tuesday's and Wednesday's records

Every Friday, I want it to return Wednesday's and Thursday's records

 

 

Your help is highly appreciated.

 

Thanks,

Mags

 

Kurt_Bremser
Super User

Then this logic does it:

data test (keep=date old_date new_date);
format date old_date new_date weekdate30.;
monday = today() - weekday(today()) + 2;
friday = monday + 4;
do date = monday to friday;
  if weekday(date) = 2
  then do;
    old_date = date - 4;
    new_date = date - 2;
  end;
  else if weekday(date) = 3
  then do;
    old_date = date - 3;
    new_date = date - 1;
  end;
  else do;
    old_date = date - 2;
    new_date = date - 1;
  end;
  output;
end;
run;

proc print data=test noobs;
run;

Result:

 

                      date                          old_date                          new_date

    Monday, April 23, 2018          Thursday, April 19, 2018          Saturday, April 21, 2018
   Tuesday, April 24, 2018          Saturday, April 21, 2018            Monday, April 23, 2018
 Wednesday, April 25, 2018            Monday, April 23, 2018           Tuesday, April 24, 2018
  Thursday, April 26, 2018           Tuesday, April 24, 2018         Wednesday, April 25, 2018
    Friday, April 27, 2018         Wednesday, April 25, 2018          Thursday, April 26, 2018

This just to show that the logic in the loop works.

 

For creation of the two macro variables, remove the do loop, set date from today(), and use call symputx() at the end.

Timbim
Obsidian | Level 7

Hi Kurt,

 

Perhaps my question should be, how do I reference the  table created from your step? My knowledge of sas is not indepth but I want to get to get to know and use sas a lot better.

 

Thanks kindly for your help.

 

Mags

Kurt_Bremser
Super User

I created table test solely for the purpose of showing that the logic works, so I could use proc print to display the values in a nice fashion.

Instead of creating a table in a loop, set variable date to a value of your chosing (eg today()), and use call symputx (see documentation) to create macro variables for further use.

Timbim
Obsidian | Level 7
Hi Kurt,

Could you please provide an example of how I could create two macro variables using the call symputx () at the end?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 30 replies
  • 2547 views
  • 3 likes
  • 5 in conversation