Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Concatenating CSV files based on their Last Modification Date

Reply
Occasional Contributor
Posts: 6

Concatenating CSV files based on their Last Modification Date

[ Edited ]

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

SAS Employee
Posts: 5

Re: Concatenating CSV files based on their Last Modification Date

Are the CSV files in a Windows operating system?
Occasional Contributor
Posts: 6

Re: Concatenating CSV files based on their Last Modification Date

Yes they are.
Super User
Posts: 19,822

Re: Concatenating CSV files based on their Last Modification Date

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
SAS Employee
Posts: 5

Re: Concatenating CSV files based on their Last Modification Date

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?
SAS Employee
Posts: 5

Re: Concatenating CSV files based on their Last Modification Date

[ Edited ]

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?

Occasional Contributor
Posts: 6

Re: Concatenating CSV files based on their Last Modification Date

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

Occasional Contributor
Posts: 6

Re: Concatenating CSV files based on their Last Modification Date

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

 

- Philip

Occasional Contributor
Posts: 6

Re: Concatenating CSV files based on their Last Modification Date

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

Super User
Posts: 11,343

Re: Concatenating CSV files based on their Last Modification Date

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.

SAS Employee
Posts: 5

Re: Concatenating CSV files based on their Last Modification Date

[ Edited ]

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;

 

 

Occasional Contributor
Posts: 6

Re: Concatenating CSV files based on their Last Modification Date

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

 

 

 

 

Ask a Question
Discussion stats
  • 11 replies
  • 461 views
  • 0 likes
  • 4 in conversation