topic Re: Recurse a Directory of CSV Files and get Row Counts in SAS Programming

Recurse a Directory of CSV Files and get Row Counts

JeffreyLowe — Wed, 25 Mar 2020 17:28:17 GMT

Greetings......

I need to loop through a folder of CSV files to get the number of rows from each file (excluding the header row) insert the file name and row count into an existing dataset and lastly delete the file.

For Example:

File1 250

insert fname rowcount into CSVRowCounts

delete File 1

File2 95

insert fname rowcount into CSVRowCounts

delete File2

and so on.....

Any assistance is greatly appreciated.

Re: Recurse a Directory of CSV Files and get Row Counts

JerryV — Wed, 25 Mar 2020 17:37:27 GMT

If this is on Linux then in a terminal session I would cd to the folder in question and:

wc -l *.csv 1>csvRowCounts.txt

rm -f *.csv

Then in SAS you can read csvRowCounts.txt into SAS and subtract 1 from each count. But I wouldn't rm the files until you are sure of your result.

Re: Recurse a Directory of CSV Files and get Row Counts

JerryV — Wed, 25 Mar 2020 17:42:21 GMT

And if it's Windows, at a command prompt cd to the directory in question and:

find /c /v "^$" *.csv >csvRowCount.txt

Read csvRowCount.txt into SAS and don't forget to subtract 1 from the count.

Re: Recurse a Directory of CSV Files and get Row Counts

JeffreyLowe — Wed, 25 Mar 2020 17:48:11 GMT

Good solutions, I should have added this is for an automated process that will run weekly.

Re: Recurse a Directory of CSV Files and get Row Counts

JerryV — Wed, 25 Mar 2020 17:54:56 GMT

Perhaps put the above OS commands, first, in the same script that launches the SAS session?

Re: Recurse a Directory of CSV Files and get Row Counts

Kurt_Bremser — Wed, 25 Mar 2020 18:41:49 GMT

Even simpler: use a pipe.

data size_and_names;
infile "cd /path;wc -l *.csv" pipe truncover;
input size fname $200.;
run;

From that dataset, it is possible to get the counts and run the fdelete.

Re: Recurse a Directory of CSV Files and get Row Counts

ghosh — Wed, 25 Mar 2020 21:55:29 GMT

This method linked below had worked for me for csv file (as well as xlsx with slight modification), you can insert code to do a row count. Seriously I wouldn't delete the the csv files in code, I would do it manually once I am satisfied the import worked

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en

There is also a post by @Tom that looks pretty good

https://communities.sas.com/t5/SAS-Enterprise-Guide/quot-Example-1-Import-All-CSV-Files-That-Exist-within-a/td-p/576286

Re: Recurse a Directory of CSV Files and get Row Counts

Ksharp — Thu, 26 Mar 2020 11:34:15 GMT

Firstly get all the pathname of these CSV file by DIR or LS command, and Run the following code.

Good Luck.

data want;
infile cards truncover;
input path $ 100.;
n_obs=0;
infile dummy filevar=path end=last;
do while(not last);
 input;
 n_obs+1;
end;
file=path;
cards;
c:\temp\have.csv
c:\temp\x.csv
c:\temp\xx.csv
;

Re: Recurse a Directory of CSV Files and get Row Counts

JeffreyLowe — Thu, 26 Mar 2020 11:41:12 GMT

Thank you everyone for your responses!