topic Re: Concatenating CSV files based on their Last Modification Date in SAS Data Science

Concatenating CSV files based on their Last Modification Date

JPOL — Fri, 09 Oct 2015 12:38:12 GMT

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]);

( 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

Re: Concatenating CSV files based on their Last Modification Date

reprui — Fri, 09 Oct 2015 13:56:21 GMT

Are the CSV files in a Windows operating system?

Re: Concatenating CSV files based on their Last Modification Date

JPOL — Fri, 09 Oct 2015 17:12:20 GMT

Yes they are.

Re: Concatenating CSV files based on their Last Modification Date

Reeza — Fri, 09 Oct 2015 17:33:35 GMT

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

Re: Concatenating CSV files based on their Last Modification Date

reprui — Fri, 09 Oct 2015 17:42:38 GMT

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?

Re: Concatenating CSV files based on their Last Modification Date

reprui — Fri, 09 Oct 2015 18:43:00 GMT

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?

Re: Concatenating CSV files based on their Last Modification Date

ballardw — Fri, 09 Oct 2015 22:16:47 GMT

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.

Re: Concatenating CSV files based on their Last Modification Date

JPOL — Mon, 12 Oct 2015 04:52:04 GMT

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

Re: Concatenating CSV files based on their Last Modification Date

JPOL — Mon, 12 Oct 2015 04:54:34 GMT

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

- Philip

Re: Concatenating CSV files based on their Last Modification Date

JPOL — Mon, 12 Oct 2015 05:09:55 GMT

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

Re: Concatenating CSV files based on their Last Modification Date

reprui — Mon, 12 Oct 2015 11:43:48 GMT

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;

Re: Concatenating CSV files based on their Last Modification Date

JPOL — Tue, 13 Oct 2015 09:31:17 GMT

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