Check if a row exists in an external excel dataset in sas

Good morning everyone,

I am new programming in sas. I have an excel (sales, for example) in which a row is inserted every day (sales per day for example) and what I want to do is run a macro in sas in which if in the excel called sales, I do not have any record of the previous day, I put a KO in another table called check sales, for example.

In summary, I want the macro to do a check on an excel dataset and if there is no record with yesterday's date do x thing (insert a KO in the column of another table) and if there is a record from yesterday , To continue

This is what I have tried. I want to see if there are records from yesterday in the excel called 'DQ_Diario'

    %macro Macro_existe2; 
        %let myfiledq= /opt/sas/dfg/dfgd/dfgd/fgdgfd/DQ_DIARIO.xlsx;
        %put &myfiledq;
        %IF (%sysfunc(fileexist(&myfiledq))) and &vweek ne &weekfin %then %do;
            %put REVISAR: existe el fichero de &vday. y es una semana entregada;
        %end;
    %end;
    %mend Macro_existe2;
    %Macro_existe2;

It should be noted that the else part is missing in the function, but right now I am interested in knowing how I can check if in an excel dataset there is the record of yesterday (yesterday I have it defined as a macrovariable so with & yesterday. It would be valid)

What I have achieved so far is that it checks for me if a file exists in a certain path, but what I need now is to check if a record exists in a file.

I hope I have explained myself clearly, thank you very much to each person who reads this post. Merry Xmas

Solution

You will need to convert the XLSX file into dataset(s) to be able to check the content. So your macro will have to generate SAS code.

Do you know the sheetname of the worksheet in the workbook that you want to check? If not PROC IMPORT will import just the first sheet whatever its name.

Is the data in a rectangular format so that it will convert easily into a dataset? Or do you want to only convert the KO column. Is KO the column header or the Excel column reference? If so do you really have that many columns in your worksheet? You can use the RANGE option of PROC IMPORT to only import specific cells.

Let's make the example simpler by assuming the column you want to check for the previous date is labeled DATE and so will be converted into a variable named DATE.

%macro check_xlsx_date(filename,mvar=found);
%if not %sysmexist(&mvar) %then %global &mvar;
%let &mvar=0;

%if %sysfunc(fileexist(&filename)) %then %do;
proc import datafile="&filename" dbms=xlsx out=excel_sheet replace ;
run;

proc sql noprint;
  select max( date = (today()-1) ) into :&mvar trimmed
    from excel_sheet
  ;
quit;
%end;
%mend check_xlsx_date;

%check_xlsx_date(/opt/sas/dfg/dfgd/dfgd/fgdgfd/DQ_DIARIO.xlsx);
%put &=found;