@Coding4you wrote: data AQ OUT OUTPUT_LOG: set AQ OUT. OUTPUT LOG: if clientname = &output name and &DPS checks ok = 1 then do: output file check="PASS"; multipin vol check = "PASS"; multipin error= "PASS"; end; else do: output file check="FAIL"; multipin vol check = "PASS"; multipin error= "PASS; end; To create that macro variable I used Proc sql; select dps_checks_ok into : dps_checks okay from output_check; quit; Show us the actual LOG from running that Proc SQL. I expect it to generate errors starting at the variable (or whatever it is supposed to be in your thinking) at the word OKAY.   When code is submitted SAS writes messages in the LOG about the code and results, including syntax problems. It is plain text. You should be able to go to the LOG window, copy text. Then on this forum open a text box by clicking on the </> icon above the message window. Then Paste the text from the log.   You still show code with syntax errors: spaces between an apparent library named OUT and a data set named OUTPUT. That code is also going to expect two data sets in the WORK library named AQ and LOG. The word NAME following &output is an error. Checks after &DPS is an error. The : at Then Do: is an error. The Output statement requires the name of a data set on the DATA statement, File is not such, neither is check=pass. The word Vol after multipin is an error. Check="Pass" is an error; The attempted assignment of "PASS" to the variable named Error after multipin is an error. In fact not a single line of your data step does not contain at least one error and that is before even seeing any likely value for the tw0 macro variables Output and DPS. ... View more

@Coding4you wrote: Thank you, I managed to compress the file so it’s like this now: DPS_cais_DPSCAIS_Interactive_Workbook_20230629.xlsm DPS_cais_DPSCAIS_Summary_Workbook_20230629.xlsm DPS_cais_DPSCAISappend_20230629.csv   would a loop like this work? data check_filenames; set DPS_FILE_COUNT; flag = 0; /* Initialize flag to 0 */ /* Loop over the records */ do until (eof); set DPS_FILE_COUNT end = eof; /* Check if the filename matches all conditions */ if filename = "DPS_cais_DPSCAIS_Interactive_Workbook_&date..xlsm" and filename = "DPS_cais_DPSCAIS_append_&date..csv" and filename = "DPS_cais_DPSCAIS_Summary_Report_&date..xlsx" then do; flag = 1; /* Set flag to 1 to indicate a "pass" */ output; /* Output the record to the check_filenames dataset */ end; end; run;   Not with AND. When you check a single variable for multiple values it will never be true for AND condition. A value cannot be 123 AND ABC at the same time.   Personally I would use flag = filename in( "DPS_cais_DPSCAIS_Interactive_Workbook_&date..xlsm", "DPS_cais_DPSCAIS_append_&date..csv" , "DPS_cais_DPSCAIS_Summary_Report_&date..xlsx" ); IN tests if a variable is one of multiple values. The result of any logical comparison in SAS is 1 or 0 so you can assign values this way skipping the if/then to assign 1/0 when true/false. ... View more

Thank you. I managed to get the file to read in. Is there a way to do a loop to check the correct files because the output name will always be different and not dps_bcs ft. The code will not work, but I was thinking something along those line. ... View more

So before you said: where sourcecode in ('125', '154', '264') and opendate >= ‘2021-03-01’ and closingdate <= ‘2023-02-07’ /* and closingdate is null */ If you are doing this in SAS then the syntax would be more like: where sourcecode in ('125', '154', '264') and opendate >= '01MAR2021'd and closingdate <= '07FEB2023'd Since SAS treats missing values as less than any valid number then "null" values of CLOSINGDATE would be included in that last AND.   If for some reason you had to push that logic into some external database that uses TRI level logic instead of normal BINARY logic you could add that extra condition this way. where sourcecode in ('125', '154', '264') and opendate >= '01MAR2021'd and (closingdate <= '07FEB2023'd or closingdate is null) ... View more

@Coding4you  - Look at the macro code I posted. ... View more