Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic import of files

Status
Not open for further replies.

evaaseow

Programmer
Jan 25, 2007
29
CA
I have a file that requires importing. The filename is relevant to the program so I would like to take that filename and extract parts of it and place them in variables. The filename changes so it cannot be hardcoded, is there a way to bring the filename in dynamically?
 
You have to provide more information. How do you intend to import a file if the name keeps changing? Unless you are planning on doing that process manually, you need to have the sas program reference the file's name.

This is the way I handle files that need to be imported. (that have a name changed)
1. You need to have an algorithm on how the filenames will change. In my case it was the date.
ex 10-31-2001.csv
I would have my SAS program expect the above mentioned file by using the date in the DATE10. format. Of course this method would only work if the current date was 10-31-2001.
Code:
*ex;
%let today = %sysfunc(putn(%sysfunc(date()),date10.));
proc import
  datafile = "&today..csv"
  out= test
  dbms=csv;
run;

There are a million other ways that you can use. Post some more info on both why you need to take filename data and how the file names will change. Perhaps we will be able to help you better with the new info.
Klaz
 
The filename is pretty dynamic.

Here is the proposed layout due to the need of the information.

Code_Cycle_Month_Year_ProgramName.txt

The code is an in house code that changes month to month.

Basically I would like to retrieve the filename into SAS and extract variables using substr to perform some data checks. Not sure if this is possible in sas.
 
Well you can do this in SAS by using the DIR (in dos\windows) or ls commands (unix).

What I would have my SAS code do is read in the filename via the directory listing of the local OS. Then I would import the data into sas in a MACRO loop. At the point of import my program will have the filename to be imported and this I would insert as a column in the current inported sas dataset. Here is an example.

DOS\windows
Code:
filename _inp pipe "dir your_source\*.csv /b /s";

data filenms;
  infile _inp;
  length myfilename $500;
  input myfilename &;
run;
data _null_;
  set filenms end=last;
  call symput('filenmx'||trim(left(put(_n_,8.))), myfilename);
  if last then
    call symput('limit',trim(left(put(_n_,8.))) );
run;

You now have a bunch of macro vars that have the filenames that you want to import.
Now Import the old way (data step vs. proc import).
Code:
%macro myloop;
  %do _ctr=1 %to &limit;

    data dsx.&_ctr;
      infile "&&filenmx.&_ctr" dlm=',';  
      input yourvars;
      fromtable = "&&filenmx.&_ctr";  
    run;  
  %end; 
%mend myloop;
Now each of your tables have a column with the name of its source. Does this help you?
 
There is also an option on the infile statement
FILENAME=, which specifies a variable into which the full filename is written (use a length statement to make sure it is long enough). You can then use the SCAN function to extract the desired parts of it in your datastep. I use it when I use a filename to read in multiple files at once...
Code:
filename allfils ("c:\temp\*.csv");
data test;
  length flnm $500 filenm $50;
  infile allfils filename=flnm recfm=v lrecl........;

  input ....
        ....;

  filenm = scan(flnm,-1,'/');
run;
If something else outside of SAS knows the name of the file (ie if it's generated by a script which then calls the SAS program) you can always pass the filename in to SAS as a SYSPARM when you call the SAS program (assuming you're using some sort of script to call the SAS prog).
Code:
  sas myprog.sas -sysparm 'param1 param2 ....'

Enjoy.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top