Hi Patrick,   You are amazing and thank you. Your code worked perfectly and I was able to complete my project successfully.     Thanks heaps! ... View more

Thanks kindly Shmuel.     ... View more

Thank you so much PG. It works perfectly.     ... View more

Hi Shmuel, It is a sas dataset and does not have a column ID. Thank you . ... View more

 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 ... View more

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   ... View more

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 ... View more

Hi Kurt, Could you please provide an example of how I could create two macro variables using the call symputx () at the end? ... View more

Hi Kurt,   Every Tuesday statement made in the first posting is incorrect. It should be as per below:   Execution Date start date end date Monday Thursday Saturday Tuesday Saturday Monday Wednesday Monday Tuesday Thursday Tuesday Wednesday Friday Wednesday Thursday   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   ... View more

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 ... View more

@ 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 ... View more

Hi Patrick, The assumption you made on the table are correct expect for Tuesday: Execution Day = Tuesday Start Day = Saturday Finish Day = Monday For Monday: Execution Day = Monday Start Day = Thursday Finish Day = Saturday The code I posted can only give me current and previous day data of other days except for Monday and Tuesday. I need help with a logic for Monday and Tuesday. To put in words: If today is Monday, give me me Thursday and Saturday’s data If today is Tuesday, give me Saturday and Monday’s days else last 2 days data. I hope this makes sense. Highly value your help. Kind regards, Mags If Tu ... View more

Hi Kurt,   Below are dummy figures in the exact same format like the real data.   Date AccNo BranchNumber AccBal 12-Apr-18 1234567 123 2345.87 13-Apr-18 1234567 123 2345.87 14-Apr-18 1234567 123 2345.87 15-Apr-18 1234567 123 2345.87 16-Apr-18 1234567 123 2345.87 17-Apr-18 1234567 123 2345.87 18-Apr-18 1234567 123 2345.87 12-Apr-18 3456789 456 5678.47 13-Apr-18 3456789 456 5678.47 14-Apr-18 3456789 456 5678.47 15-Apr-18 3456789 456 5678.47 16-Apr-18 3456789 456 5678.47 17-Apr-18 3456789 456 5678.47 18-Apr-18 3456789 456 5678.47   Thanks kindly, Mags ... View more

Hi Kurt,   Below is the current example and please note every Mondays, the 'old date' must be Thursdays and the 'new date' must be Saturdays. Every Tuesdays, the 'old date' must be Saturdays and 'new date' must be Mondays. Every other day from Wed-Friday 'old date' is today() minus 2 and 'new date' is today() minus 1.   * 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;   I can easily use the intnx function to do Wed-Fri like below but having issue trying to figure out a way to do Mon and Tues.   For example: Proc Sql; Create Table DAILY_T as Select Date,  (case when Date = today()2 then 'Previous' else 'Current' end) as Period,   AccNo, BranchNumber, AccBal   From ACCOUNT_DAILY_TABLE where Date >=intnx ('day',today(),-2); Quit;     Can you please help me with a function or data step to do all in once:   if Monday, then 'old date' is Thur and 'new date' is Saturday if Tuesday, the 'old date' is Saturday and 'new date' is Monday else 'old date' is today() minus 2 and 'new date' is today() minus 1   ----OUTPUT----- The output has to have 2 records of the same account - 1 record for 'old date' and 1 record for 'new date'   Highly value your help.   Thanks kindly, Mags   ... View more