Sas code to compare previous day files using import.

[User_2024]

Hi, I am a beginner to SAS and I really appreciate if I can get suggestions for this problem. 

I have a SAS code that runs daily except on weekends. Everyday when it runs the output is saved in a location with &today which means yesterday date. example filepath\output\filename_27FEB2024 for running it today. Now, I want to compare today's report with yesterdays and remove duplicates based on acct_id. this code works for just comparing today and tomorrow. but it picks up data from day before yesterday and I dont know how to compare all the previous day files to todays and remove the dupes based on acct_id. pls help, here are the sample macros and code that i use now 

%let current_day = %sysfunc(today());
%let weekday = %sysfunc(weekday(&current_day.));

%if &weekday. = 2 %then %do;
/* If it's Monday, fetch Thursday */
%let previous_day = %sysfunc(intnx(day, &current_day., -4), date9.);
%end;
%else %do;
/* For the rest of the week, refer to yesterday */
%let previous_day = %sysfunc(intnx(day, &current_day., -2), date9.);
%end;

%put &previous_day.;

/*%let previous_day = %sysfunc(intnx(day, %sysfunc(today()), -2), date9.);*/
/*%put &previous_day;*/

PROC IMPORT
DATAFILE = "filepath\Output\filename&previous_day..xlsx"
OUT = Previous_report1
DBMS = EXCEL REPLACE;
RANGE = 'possible_accts$';
MIXED = YES;
SCANTEXT = YES;
USEDATE = YES;
SCANTIME = YES;
RUN;

rsubmit;
proc upload data=Previous_report1 ;
run;
endrsubmit;

rsubmit;
data Previous_report (keep = acct_id);
set Previous_report1;
by acct_id;
run;
endrsubmit;

[ballardw]

Current day -1 is yesterday, not -2.

Look at your INTNX code.

[User_2024]

my everyday files are saved in today-1 as file name , if it runs today, file name is saved as 02272024, so if i want to use &previous_day using intnx function, i gotta go -2 instead of -1. 

let previous_day = %sysfunc(intnx(day, &current_day., -2), date9.);

%put &previous_day.;

this will give me &previous_day. as 26feb2024 for today. I hope this helps

%let today = %sysfunc(putn(%eval(%sysfunc(today())-1),date9.));
%put &today;
%let previous_day = %sysfunc(intnx(day, %sysfunc(today()), -2), date9.);
%put &previous_day;

[ballardw]

---

@User_2024 wrote:

my everyday files are saved in today-1 as file name , if it runs today, file name is saved as 02272024, so if i want to use &previous_day using intnx function, i gotta go -2 instead of -1. 

let previous_day = %sysfunc(intnx(day, &current_day., -2), date9.);

%put &previous_day.;

this will give me &previous_day. as 26feb2024 for today. I hope this helps

%let today = %sysfunc(putn(%eval(%sysfunc(today())-1),date9.));
%put &today;
%let previous_day = %sysfunc(intnx(day, %sysfunc(today()), -2), date9.);
%put &previous_day;

---

Not the code posted in the original post where you are using CURRENT_day defined as today. And then calculated offset from Current_day.

Calling something "today" with a value of "yesterday" is just asking for confusion in multiple places. So I'm more confused then before. If you did decrement "today" to yesterday then you do not want to decrease that by 2, you still want 1.

Instead of completely separate %if/%then code calculating the Intnx I would suggest calculating one variable for the offset and then use the one assignment to previous using the proper offset. Then it would be easy to confirm/test the correct OFFSET value for any given day/date.

[User_2024]

so, I took the advice and changes few things how i define the date and how i save the file. made the below changes for daymacros.  with that, I stacked datasets and compared it. that did wonder! 

%let current_day = %sysfunc(today());

%let weekday = %sysfunc(weekday(&current_day.));

%if &weekday. = 2 %then %do;

    /* If it's Monday, fetch friday */

    %let previous_day  = %sysfunc(intnx(day, &current_day., -3), date9.);

%let previous_day2 = %sysfunc(intnx(day, &current_day., -4), date9.);

%let previous_day3 = %sysfunc(intnx(day, &current_day., -5), date9.);

%let previous_day4 = %sysfunc(intnx(day, &current_day., -6), date9.);

  %let previous_day5 = %sysfunc(intnx(day, &current_day., -7), date9.);

%let previous_day6 = %sysfunc(intnx(day, &current_day., -8), date9.);

%let previous_day7 = %sysfunc(intnx(day, &current_day., -9), date9.);

%end;

%else %do;

    /* For the rest of the week, refer to yesterday */

    %let previous_day  = %sysfunc(intnx(day, &current_day., -1), date9.);

%let previous_day2 = %sysfunc(intnx(day, &current_day., -2), date9.);

%let previous_day3 = %sysfunc(intnx(day, &current_day., -3), date9.);

%let previous_day4 = %sysfunc(intnx(day, &current_day., -4), date9.);

  %let previous_day5 = %sysfunc(intnx(day, &current_day., -5), date9.);

%let previous_day6 = %sysfunc(intnx(day, &current_day., -6), date9.);

%let previous_day7 = %sysfunc(intnx(day, &current_day., -7), date9.);

%end;

%put &previous_day.;

[Tom · Accepted solution]

There is no need to use INTNX() when the interval you are moving is the same as the units the value is stored in.  You can just use regular arithmetic.

But for your problem perhaps you want to use INTNX() with the WEEKDAY interval instead.  That will skip SAT and SUN.

data days;
  date1=intnx('weekday',date(),-1);
  do offset=-1 to -8 by -1;
    date2=intnx('weekday',date1,offset);
    output;
  end;
  format date1 date2 weekdate.;
run;

Result:

Obs               date1                offset               date2

 1     Wednesday, February 28, 2024      -1        Tuesday, February 27, 2024
 2     Wednesday, February 28, 2024      -2         Monday, February 26, 2024
 3     Wednesday, February 28, 2024      -3         Friday, February 23, 2024
 4     Wednesday, February 28, 2024      -4       Thursday, February 22, 2024
 5     Wednesday, February 28, 2024      -5      Wednesday, February 21, 2024
 6     Wednesday, February 28, 2024      -6        Tuesday, February 20, 2024
 7     Wednesday, February 28, 2024      -7         Monday, February 19, 2024
 8     Wednesday, February 28, 2024      -8         Friday, February 16, 2024

[Reeza]

Rather than determine file names is it possible to scan the folder and get the latest two file dates?

[User_2024]

but I want to scan last 6 days of files before setting my output for today. because right now, my code looks at yesterdays, and if there is duplicate say ALL of them are duplicates, instead of spitting empty cells, it goes and fetches the prev output data and prints it.

[Tom]

Still not seeing the whole picture here.  Before trying to figure out how to get the macro processor to generate code it really helps if you know what code you want to generate. 

So assuming you want to compare the runs for 28FEB2024 to the runs for 27FEB2024 and 26FEB2024 can you show the SAS code you would run?

As to your issue of finding 6 or 7 dates it is also going to be much easier to do it with SAS code than with MACRO code.  I assume the %IF statement you had as to skip week ends?  If so then something like this should find the 7 most recent dates for you.

data dates;
  today=date();
  offset=0;
  do run=1 to 7;
    offset+1;
    day = weekday(today-offset);
    if day = 1 then do;
      offset+2;
    end;
    date = today - offset;
    day = weekday(date);
    output;
  end;
  format today date date9.;
run;

Result

Obs        today    offset    run    day         date

 1     28FEB2024       1       1      3     27FEB2024
 2     28FEB2024       2       2      2     26FEB2024
 3     28FEB2024       5       3      6     23FEB2024
 4     28FEB2024       6       4      5     22FEB2024
 5     28FEB2024       7       5      4     21FEB2024
 6     28FEB2024       8       6      3     20FEB2024
 7     28FEB2024       9       7      2     19FEB2024

TODAY is Wednesday so the previous 7 days are Tuesday, Monday, Friday, Thursday, Wednesday, Tuesday and Monday

[User_2024]

Thank you, I made the changes to the &today, &previous_day, it worked .

[Tom]

I am not sure what the question is.

It looks to me like you have figured out how to find the previous XLSX file.

So what it it you need help with?

Are you just looking to see if the set of ACCT_ID values is different?  Do you need to know which ACCT_ID were ADDED and which where REMOVED and which were in both sets?

data want;
  merge old(in=in1) new(in=in2);
  by acct_id;
  if in1 and in2 then type='BOTH';
  else if in1 then type='OLD';
  else type='NEW';
run;

Do you also need to check if whether any of the variables with actually data about the account has changed?  That is probably a question for PROC COMPARE. 

[User_2024]

Thanks for replying, my issue is not comparing today's output(excel) with yesterday's. i want to compare today's with yesterdays and all of the 6 days prior so I dont have duplicates from any of the files. Currently, my code looks at yesterdays using proc import and eliminates duplicates BUT it is bringing day before yesterday's output to today's. I am so stuck. . 

my output files are saved as below . for todays run the file saved in yesterdays date. and so on. Before it spits the output for today, it imports the previous day file (26feb) checks for duplicates, avoids it. Now, I want to do this for ALL of the previous files so I dont have ANY duplicates from any of the prev files. Hope this helps. I really appreciate all of your time and help. 

output_27feb2024

output_26feb2024

output_25feb2024

output_22feb2024

output_21feb2024

[Reeza]

---

@User_2024 wrote:

Thanks for replying, my issue is not comparing today's output(excel) with yesterday's. i want to compare today's with yesterdays and all of the 6 days prior so I dont have duplicates from any of the files. Currently, my code looks at yesterdays using proc import and eliminates duplicates BUT it is bringing day before yesterday's output to today's. I am so stuck. . 

my output files are saved as below . for todays run the file saved in yesterdays date. and so on. Before it spits the output for today, it imports the previous day file (26feb) checks for duplicates, avoids it. Now, I want to do this for ALL of the previous files so I dont have ANY duplicates from any of the prev files. Hope this helps. I really appreciate all of your time and help. 

output_27feb2024

output_26feb2024

output_25feb2024

output_22feb2024

output_21feb2024

---

Import all the files and stack them using INDSNAME option on the SET statement to add in the filename and extract the date in a new variable.

Then sort by ID and descending date and take the first record per ID, then you'll have the latest record for each ID across the 6 files.

[Reeza]

To expand on my previous response, I would recommend:

• Reading each file every day and storing as rawData_20240130 (Change date format to YYMMDD for easier usage)
• Once the files are read into a SAS library and stored correctly then have a second process that uses sashelp.vtable to find the last 6/7 tables. If the dates are in YYMMDD, this is via a sort.

proc sql;
select catx(".", libname, memname) into :table_list
separated by " " from sashelp.vtable where libname='MYLIB' 
order by memname desc;
quit;

%put &table_list;

• Then append all the data together and add the date field in

data combined;
set &table_list insdsname=src;

date=scan(src, -1, "_");
run;

• Then sort the file and take the latest/earliest record using first/last (I'll assume you know how to do this step)