BookmarkSubscribeRSS Feed
JPOL
Calcite | Level 5

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

11 REPLIES 11
reprui
SAS Employee
Are the CSV files in a Windows operating system?

Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

Reeza
Super User
You need to post this in a JMP forum, the answers here will be SAS Specific, unless I'm missing something. https://community.jmp.com/community/discussions
reprui
SAS Employee
The technique I would suggest would be Base SAS code as this will be an OS level solution using the SYSTASK (or X) command along with a series of file level analytics. I understand this to be about file evaluation and preparation...not JMP analysis...Right?

Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

reprui
SAS Employee

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

JPOL
Calcite | Level 5

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

JPOL
Calcite | Level 5

Yes, this is about file preparation prior to JMP analysis.

 

- Philip

JPOL
Calcite | Level 5

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 Smiley Happy

 

Rgds,

 

Philip

ballardw
Super User

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.

reprui
SAS Employee

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

JPOL
Calcite | Level 5

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

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

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.

Discussion stats
  • 11 replies
  • 1489 views
  • 0 likes
  • 4 in conversation