Hi. I have multiple csv files named as:
q1_2012_all
q2_2012_all
q3_2012_all
q4_2012_all
q1_2013_all
...
q2_2021_all
Does anyone know how to read them and combine them?
Thank you!
A recent similar request
https://communities.sas.com/t5/SAS-Programming/macro-for-load-in-multiple-files-csv/m-p/763795
The key bit here is getting the names of the files into a data set with the Path information.
The example uses SAS on Demand which starts the users path with ~ . If running in a windows system start with a drive letter.
If you are running on a server version of SAS then the files have to be in a location that the Server can see with the path which would likely not be the case of reading files on your computer unless a network share of some sort has been set up.
You can use a filename statement with wildcards to select the files and on the infile statement you need to make use of the following properties:
firstobs=2 (to start reading data from the second line)
eov specifies a variable that SAS sets to 1 when the first record in a file in a series of concatenated files is read .
The code below is a solution to your problem (it assumes you have 3 variables a,b,c and they are all numeric):
%let csvFolder= <Path to the directory containing the csv files>;
filename ttt "&csvFolder.\q*_all.csv" ;
data CSVIN;
infile ttt truncover firstobs=2 dlm=',' dsd eov=_eov ;
input @ ;/*Start read line and hold*/
if _eov then do ; /*If if 1st row of file */
_eov = 0 ;/* EOV = 1 after first row of next file and needs to be reset to 0 */
delete ; /*delete the 1st row of each file because it is the header*/
end ;
input a b c;
run;
The option firstobs is not useful when reading multiple files with one infile statement, because the option does not work per file.
%let path=c:\temp ; data _null_; rc=filename('x',"&path"); did=dopen('x'); do i=1 to dnum(did); fname=dread(did,i); if scan(fname,-1,'.')='csv' then call execute(catt("proc import datafile='&path\",fname, "' out=",scan(fname,1,'.')," dbms=csv replace;run;")); end; run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: