BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JeffreyLowe
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ghosh
Barite | Level 11

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&docse...

 

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-w...

View solution in original post

8 REPLIES 8
JerryV
SAS Employee

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.

 

JerryV
SAS Employee

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.

JeffreyLowe
Obsidian | Level 7
Good solutions, I should have added this is for an automated process that will run weekly.
JerryV
SAS Employee
Perhaps put the above OS commands, first, in the same script that launches the SAS session?
Kurt_Bremser
Super User

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.

 

ghosh
Barite | Level 11

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&docse...

 

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-w...

Ksharp
Super User

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
;

  

JeffreyLowe
Obsidian | Level 7

Thank you everyone for your responses!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 3327 views
  • 6 likes
  • 5 in conversation