topic Re: Import data from folders in SAS Procedures

Import data from folders

Q1983 — Sun, 28 Jan 2018 22:24:37 GMT

　%let myPath =/myshare/HHFit;

%put &myPath;

proc import out= AllState

datafile="&myPath/GA/GA_HHF_STM_ToState_&Currdate_key..csv"

dbms=CSV replace;

run;

1. GA stands for Georgia, one of 19 state folders that appear in myshare/HHFit. Each state has its own folder and .csv file that must be imported every day

2. The .csv files need to be read starting at the 4th row or firstobs = 4. I believe I need a delimiter = '' and missover statement but get errors when I add it

3. Since I have 19 folders within the myshare/HHFit share drive is there a way to bring in all 19 folder contents or must I use 19 different proc import statements

4. When I read in the data I will have only rows of data and columns. Is there a way to also create a shell dataset and populate the results.

ie data allstate

servicer
id

address

Re: Import data from folders

Reeza — Mon, 29 Jan 2018 01:14:18 GMT

Those are your requirements. What's your question for the forum? What have you tried? What isn't working?

Re: Import data from folders

ballardw — Mon, 29 Jan 2018 15:30:07 GMT

@Q1983 wrote:

　%let myPath =/myshare/HHFit;

%put &myPath;

　

proc import out= AllState

datafile="&myPath/GA/GA_HHF_STM_ToState_&Currdate_key..csv"

dbms=CSV replace;

run;

1. GA stands for Georgia, one of 19 state folders that appear in myshare/HHFit. Each state has its own folder and .csv file that must be imported every day

2. The .csv files need to be read starting at the 4th row or firstobs = 4. I believe I need a delimiter = '' and missover statement but get errors when I add it

3. Since I have 19 folders within the myshare/HHFit share drive is there a way to bring in all 19 folder contents or must I use 19 different proc import statements

4. When I read in the data I will have only rows of data and columns. Is there a way to also create a shell dataset and populate the results.

ie data allstate

servicer
id

address

Are all of these files in the same layout (same number of variables, same meaning / type of variable).

the Proc IMPORT is not for you . Data step code will work better in that all the variables would have the same type and length. You can use Proc import to give you the basic data step code, it will appear in the log and can be copied into the editor and desired changes made. I suggest going through the import wizard to show where to set your first obs option and to use a large value of guessingrows for the first one to have a better chance on identifying the lengths of columns.

Then you would replace modify the infile statement and output data set name for each file to read.

this line in any case should be reviewed:

datafile="&myPath/GA/GA_HHF_STM_ToState_&Currdate_key..csv"

should be

datafile="&myPath./GA/GA_HHF_STM_ToState_&Currdate_key..csv"

as forgetting that first dot could create some interesting errors in some useages.

Re: Import data from folders

Satish_Parida — Tue, 30 Jan 2018 09:09:52 GMT

I assume that the code you written is working.
remove the replace in the code.
if you are in linux then use find /pathname/ -name "*.csv" as pipe to get all the CSV file in the parent directory. In your case pathname is /myshare/HHFit

Put your code inside a macro like following, I have skipped the part of importing filenames to the DS.

%macro ImportCSV(MyFile=);

proc import out= AllState
datafile="&MyFile"
dbms=CSV;
run;

%mend;

Data _null_;
set listofFiles;
command2execute='%ImportCSV('||filenames||');';
call execute(command2execute);
run;

Please let me know if it works out for you.