About Coding4you

Coding4you · ‎07-10-2023

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;

Coding4you · ‎07-10-2023

So the purpose of the checks is to identify if the correct number of excel files has been outputted in the folder. The output name always changes depending on the input name. I’ve already built this macro variable in so that it scan the input name. For every input name e.g. A we expect to see three files outputted: The only thing that changes is the macro variable output name and date . If there is two Input file with input name A&B we should see six files. "&output_name._DPSCAIS_Interactive_Workbook_&date..xlsm" or filename = "&output_name. DPSCAIS append_&date..csv" or filename = "&output_name._DPSCAIS_Summary_Report_&date..xlsx" I think I managed to find the solution where I can create a table base of all the output filenames at that particular time and compare it with the what’s in the folder.

Coding4you · ‎07-10-2023

these are the files I managed to read from the folder. DPS_File_count table Filename DPS_cais_DPSCAIS_Interactive_Workbook_20230629.xlsm DPS_cais_DPSCAIS_Summary_Workbook_20230629.xlsm DPS_Check_filenames table Filename Filename Flag DPS_cais_DPSCAIS_Interactive_Workbook_20230629.xlsm DPS_cais_DPSCAIS_Interactive_Workbook_20230629.xlsm 1 DPS_cais_DPSCAIS_Summary_Workbook_20230629.xlsm DPS_cais_DPSCAIS_Summary_Workbook_20230629.xlsm 1 File_compare table (blank) In the loop part of the codes the filename DPS_cais_DPSCAIS_append_20230629.csv is missing in the table which meant that the result will flag as pass instead of fail if that make sense

Coding4you · ‎07-10-2023

Hi, I'm trying to write a SAS code where I can count how many files exist in that folder. However, I'm struggling to execute the logic behind the code. In the scenario the output name =" dps_cais ". the output name is base on different names, which is why it's in a macro variable. I'm trying to work out a solution to flag up as fail if it does equal the filename (see the code below) which should be always be 3 per output_name. data dps_file_count; set WORK.FILENAME; WHERE filename like "%DPSCAIS%" ; data dps_file_count; set DPS_FILE_COUNT; WHERE filename NOT like "%Rossen%"; /*Creating a output name loop unitl it matches the filename */ data check_filenames; set DPS_FILE_COUNT; flag = filename; do until (flag = filename or eof); set DPS_FILE_COUNT end = eof; if filename = "&output_name._DPSCAIS_Interactive_Workbook_&date..xlsm" or filename = "&output_name. DPSCAIS append_&date..csv" or filename = "&output_name._DPSCAIS_Summary_Report_&date..xlsx" then flag = 1; end; run; /*check what files is missing*/ PROC SQL; create table file_compare as SELECT b.filename from check_filenames b where filename not in (select filename from dps_file_count a where a.filename = b.filename); quit; PROC SQL; create table COUNT_FILES AS SELECT COUNT(filename) AS COUNTS FROM file_compare ; DATA CHECK_NUM_FILES; SET file_compare; IF COUNTS >= 1 THEN FILE_COUNT = "FAIL" ;ELSE FILE_COUNT = "PASS";

Coding4you · ‎07-06-2023

Dps check is the variable name in the output check table and it will only have two values 0 or 1. the select into will only return the values. I think it maybe easier to merge it, because I’m trying to perform if &dps_check = 1 then do in another data step which end up 1=1 and it wouldn’t work

Coding4you · ‎07-06-2023

Yes I noticed that. the question is how do I reference that in another data step? Because the dps_checks value is one in the output check table. I want to use that so that if it’s 1 then do this if that make sense?

Coding4you · ‎07-06-2023

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;

Coding4you · ‎07-06-2023

real time : 0.054 cpu time : 0.046 MPRINT (DPS EXPIN REFORMAT):data AQ OUT.OUTPUT_LOG; MPRINT (DPS EXPIN REFORMAT):set AQ OUT. OUTPUT LOG SYMBOLGEN: Macro variable dps checks ok resolved to 1 MPRINT (DPS EXPIN REFORMAT): DPS checks ok= 1; SYMBOLGEN: Macro variable output name resolved to DPS_KEL_TEST SYMBOLGEN: Macro variable DPS checks ok resolved to 1 MPRINT (DPS EXPIN REFORMAT) : if clientname = DPS KEL TEST and 1 = 1 Then do; MPRINT (DPS EXPIN REFORMAT) : output file check="PASS"; MPRINT (DPS EXPIN REFORMAT) : multipin vol check = "PASS"; MPRINT (DPS EXPIN REFORMAT) : multipin error= "PASS"; MPRINT (DPS EXPIN REFORMAT): end; MPRINT (DPS EXPIN REFORMAT): else do; MPRINT (DPS EXPIN REFORMAT) output file check="FAIL";: MPRINT (DPS EXPIN REFORMAT): multipin vol check = "PASS"; MPRINT (DPS EXPIN REFORMAT): multipin error= "PASS"; MPRINT (DPS EXPIN REFORMAT) : end; MPRINT (DPS EXPIN REFORMAT):run; NOTE: 13 observations were read from "AQ OUT,OUTPUT LOG" NOTE: Data_set "AQ_OUT.OUTPUT_LOG" has 13 observation (s) and 17 variable (3) NOTE: Specifying compression for data set "AQ_OUT-OUTPUT_LOG" has not changed pages NOTE: The data step took : real time 0.052 size of 3

Coding4you · ‎07-06-2023

Thanks Paige. please see the text file

Coding4you · ‎07-06-2023

I have a table name output_check which have a variable call dps_checks. in the output log, how can I reference that dps_checks so that this piece of code works : thank you.

Coding4you · ‎06-30-2023

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.

Coding4you · ‎06-27-2023

Hi, I’m trying to set up an output check that counts how many Excel files it has outputted at that particular time. The filename will always have DPSCAIS append, DPSCAIS_interactive, and DPSCAIS_summary. The only differences are the dates and the name at the start. My question is there a way to count how many of these files are outputted in that folder with the file name mentioned? It should always be three files outputted, if it doesn’t have that file name then flag as fail in the output file check. say the the folder is “ukfh\file\data\outbox” Thank you

Coding4you · ‎02-07-2023

Yes I’m trying to extract anything that fall between those dates and if closing date is null when open date >= ‘2021-03-31’ if that make sense.

Coding4you · ‎02-07-2023

Thank you for your speedy response! Sorry for the confusion. What I was trying to do was extract all the source code that was between those dates. However, if I ran the code where opendate >= '03JAN2021'and closingdate <= '07FEB2023’ it will only provide the dates that are within those specific dates, which is correct. What I wanted to include was that when there are records where there are null values in the closing date but not the open date, and vice versa within the same step, which won't work as it wouldn't be able to satisfy both conditions as mentioned below. i was thinking whether there's a quick way to get all that information in a few steps from the same table. i hope they

Coding4you · ‎02-07-2023

Hi All, Please see the below codes. I’m trying to extract the data between those dates and that if there is a null value in both closing and open date. Is there a way to specify that in the below codes? Thanks Proc sql; Create or replace table Cais as Select sourcecode, opendate, closingdate from Expin where sourcecode in (‘125’, ‘154’, ‘264’) and opendate >= ‘2021-03-01’ and closingdate <= ‘2023-02-07’ and closingdate is null ; Quit; I

Online Status	Offline
Date Last Visited	‎07-12-2023 09:59 AM

Re: check number of files

Re: check number of files

Re: check number of files

check number of files

Re: Referencing macro variable

Re: Referencing macro variable

Re: Referencing macro variable

Re: Referencing macro variable

Re: Referencing macro variable

Referencing macro variable

Re: check number of files

Re: Is there a way to count how many excel files in a file directory

Re: Is there a way to count how many excel files in a file directory

Re: Why is it not returning null values

Re: Why is it not returning null values

Re: check number of files

Re: check number of files

Re: check number of files

check number of files

Re: Referencing macro variable

Re: Referencing macro variable

Re: Referencing macro variable

Re: Referencing macro variable

Re: Referencing macro variable

Referencing macro variable

Re: Is there a way to count how many excel files in a file directory

Is there a way to count how many excel files in a file directory

Re: Why is it not returning null values

Re: Why is it not returning null values

Why is it not returning null values