Help using Base SAS procedures

Import data from folders

Reply
Regular Contributor
Posts: 179

Import data from folders

 %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

 

Super User
Posts: 23,758

Re: Import data from folders

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

 

 

Super User
Posts: 13,574

Re: Import data from folders


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.

Frequent Contributor
Posts: 109

Re: Import data from folders

  1. I assume that the code you written is working.
  2. remove the replace in the code.
  3. 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
  4. 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.
Ask a Question
Discussion stats
  • 3 replies
  • 157 views
  • 0 likes
  • 4 in conversation