Hi,
I am a new JMP12 user and I have a need to concatenate several hundred .CSV files every day in order to monitor a manufacturig tool's performance.
Now that the tool has been in use for several months I currently have ~ 75 000 files on the directory from where I need to read the files.
All files need to remain on this directory.
I need to monitor the tool over the last 30 days or so. Opening and concatenating the latest 2000 files would meed my needs.
How can I add to the filelist to be opened based on the files' Last Modification Date?
I would like to add an additional time based filter to those already in use:
filepath = "K:\...\PROBER_01\" ;
prefilelist = Files In Directory( filepath );
n2=nitems(prefilelist);
//Provide filters to ensure appropriate csv files
For( i2 = 1, n2 >= i2, i2++,
file=(prefilelist[i2]);
If
( word( 3, prefilelist[i2], "_" ) == "DRIE"
& word( 2, prefilelist[i2], "_" ) == "ILGO1"
& substr(prefilelist[i2],5,1 ) == "L"
& substr(prefilelist[i2],1,1 ) == "G",
Insert Into( filelist,file),
show(file)
)
);
something like :
& Last Modification Date() > today()-30 days ??????
Thanks,
Philip
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
JPOL...Can you provide an example of the File Names? Are they incrementally (or randomly) numbered? Or, does the file name contain a date/time stamp?
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Hi reprui,
Here is an example:
Filename Last Modified
B35L1007_BK1_FINAL01.csv 10/11/2015 7:23
B36L0216_BK1_GRID25.csv 10/10/2015 22:46
B36L0221_BK1_FINAL25.csv 10/11/2015 4:42
B38L0920_BK1_FINAL25.csv 10/11/2015 18:37
BN30L2515_BO1_FINAL40.csv 10/12/2015 3:31
BN30L2524_BO1_FINAL40.csv 10/12/2015 3:35
BN31L1606_BO1_FINAL01.csv 10/11/2015 10:01
BN31L1625_BO1_FINAL01.csv 10/11/2015 20:48
GK39L0507_GK1_GRID01.csv 10/12/2015 3:59
GK39L0607_GK1_GRID01.csv 10/11/2015 2:30
GN36L0411_ILGO1_DRIE_25.csv 10/11/2015 11:23
GN36L0421_ILGO1_DRIE_25.csv 10/11/2015 11:58
GN38L0319_GO2_FINAL40.csv 10/10/2015 23:48
GN38L0324_GO1_FINAL40.csv 10/10/2015 22:11
GN38L1102_GO2_FINAL25.csv 10/11/2015 15:54
GN38L1120_GO2_FINAL25.csv 10/12/2015 5:55
GN39L0413_GO2_FINAL40.csv 10/12/2015 7:20
GN39L0415_GO2_FINAL40.csv 10/12/2015 7:46
GN39L0418_GO1_FINAL40.csv 10/12/2015 4:22
GN39L0422_GO2_FINAL40.csv 10/12/2015 7:36
GN39L0425_GO1_FINAL40.csv 10/12/2015 4:31
GN39L0525_ILGO1_DRIE_25.csv 10/10/2015 19:51
GP38L0214_GP1_GRID01.csv 10/12/2015 6:29
GP38L0222_GP1_GRID01.csv 10/12/2015 6:39
GP38L0405_GP1_GRID01.csv 10/11/2015 23:17
GP38L0408_GP1_GRID01.csv 10/12/2015 0:36
As you see, it is a combination of incrementally and randomly numbered, based on the LOT IDs and measurement phase.
- Philip
Yes, this is about file preparation prior to JMP analysis.
- Philip
Thanks Reeza for your suggestion.
I have copied my question over to the JMP community but I am still open to any suggestions which may come from this community
Rgds,
Philip
I would be strongly tempted to pipe the output of a DIR command with appropriate switches into a text file which I could read and use for a control file. Something like
DIR K:\...\PROBER_01\*.csv /O-D -> c:\filelist.txt
which would have a list of newest to oldest files (or /OD oldest to newest) with date, size and filename.
I'm not sure how in JMP to read or parse but the date and name information based on today's date but expect it to be possible.
Yes...ballardw is advising in the right direction...Below is code written in Base SAS that should acheive your expected results. This should work in a Base SAS code window in JMP. It uses sample data from the SASHELP library...
/*Program created to help with a SAS Communities question*/
Options symbolgen mprint mlogic mrecall nosyntaxcheck;
/* BEGIN - Create the library of test Files*/
systask command
'mkdir "C:\CSVFLSOut"'
wait
;
systask command
'mkdir "C:\CSVResults"'
wait
;
libname RESULTS "C:\CSVResults";
libname CSVFLS "C:\CSVFLSOut";
%Macro CreateCSVFiles;
%Do I=1 %to 20;
Data CSVOut&i;
set sashelp.baseball;
run;
ods csv file="C:\CSVFLSOut\CSVOut&i..csv";
proc print data=CSVOut&i;
run;
%End;
ods _all_ close;
%mend CreateCSVFiles;
%CreateCSVFiles;
/* END - Create the library of test Files*/
ods listing;
/* BEGIN - Acquire a list of the desired files to process from the test directory/files. */
%Macro DOSCommands;
systask command
'dir "C:\CSVFLSOut\*.csv " >"C:\CSVResults\CSVFLS_CodeCount.txt"'
wait
;
%Mend DOSCommands;
%DOSCommands;
filename RESULTS1 "C:\CSVResults\CSVFLS_CodeCount.txt";
data CSVFilesCnt;
infile RESULTS1 dsd truncover;
input
@1 FileDate mmddyy10. @;
If FileDate^=.
;
input
@13 FileTime TIME.
@40 FileName $40.
;
format
FileDate mmddyy10.
FileTime TIMEAMPM8.
;
/*BEGIN - Select the files to append based on the last modified date*/
if intck('Month',FileDate,Today())<30;
/*END - Select the files to append based on the last modified date*/
Call Symput('FileCnt',_n_);
run;
data _null_;
set CSVFilesCnt;
if intck('Month',FileDate,Today())<30;
Call Symput('FileCnt',_n_);
run;
%put &FileCnt;
/* END - Acquire a list of the desired files to process from the test directory/files. */
/* BEGIN - Create the Macro Variable File Name references to use in the SAS DataSet Creation and Append steps. */
%Macro CreateMacroFileNames;
%Do i=1 %to &FileCnt;
data _Null_;
Set CSVFilesCnt;
If _n_=&i;
Call Symput("FileName&i",FileName);
run;
%put &&FileName&i;
/* END - Create the Macro Variable File Name references to use in the SAS DataSet Creation and Append steps. */
/*BEGIN - Create a SAS dataset of each CSV File to be appended.*/
filename CSVFile "C:\CSVFLSOut\&&FileName&i";
Data CSV&i;
infile CSVFile dsd truncover;
Input @1 FullRecord $1000.;
run;
/*END - Create a SAS dataset of each CSV File to be appended.*/
/*BEGIN - Append the CSV Files into one SAS Dataset.*/
Proc Append Base=BigFileAll Data=CSV&i FORCE;
/*END - Append the CSV Files into one SAS Dataset.*/
%end;
%Mend CreateMacroFileNames;
%CreateMacroFileNames;
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Thank you all for your suggestions.
A working solution has been provided by MS over in the JMP community.
For those who are interested, I am including it here:
filepath = "K:\....\PROBER_01\";
prefilelist = Files In Directory(filepath);
n2 = N Items(prefilelist);
datelimit = Today() - In Days(30);
filelist = {};
For(i2 = 1, i2 <= n2, i2++,
file = prefilelist[i2];
If(
Word(3, file, "_") == "DRIE" &
Word(2, file, "_") == "ILGO1" &
Substr(file, 5, 1) == "L" &
Substr(file, 1, 1) == "G",
If(Last Modification Date(filepath || file) > datelimit,
Insert Into(filelist, file);
Show(file);
);
);
Best Regards,
Philip
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.