Importing file without knowing the name

[JonathanV]

Hi!

I hope someone can help me.

In my SAS project I need to program (via datastep or proc import) that I import every week a certain file, which has a different name each week.

The location of the file is in the same map.

Is there a method to do this?

greets

[andreas_lds]

I don't understand:

---

@JonathanV wrote:

The location of the file is in the same map.

---

What does "map" mean in the context of importing files?

Working with Enterprise Guide, you could use the Prompt Manager to ask the user which file she/he wants to process.

[ballardw]

Does this file have the same structure every time? Then you do not want "import", i.e. use Proc Import, to read the file as the procedure makes guesses as to variable name, type and length for each file. Then when you try ti combine the data sets you have issues with incompatible variable types or truncated data.

A data step to read the file is much preferred. Debut once and get data sets that won't cause problems later.

Is there any sort of pattern to the filename that tells you which file you want to read? Examples of names would be good.

[JonathanV]

*The map (folder) is always the same

*The structure and variables are always the same in csv

*The beginning of the name is always the same, only the end had a time/data stamp which is unique

[andreas_lds · Accepted solution]

---

@JonathanV wrote:
*The map (folder) is always the same

*The structure and variables are always the same in csv

*The beginning of the name is always the same, only the end had a time/data stamp which is unique

---

Then again: why not using the prompt manager? Letting the user choose the file reduces the complexity of the program to naught.

The naming of you files makes it unnecessary difficult to automate the selection of the file to be imported. You could read all filenames using a data step, extract the date. To find the file to read, translate that date to the week-number and compare that to the current week-number.

If it always the latest file and xcmd is active, you should use bash / powershell command to get the name of that file.

[Osprey06]

Will the file have a consistent name/format each week? I.e. week1.xlsx, week2.xlsx etc.? If so, you should be able to write something to account for that (e.g. to use the Monday before the run date as your input date or similar). If you're unsure of how to work the logic and would like assistance on that, you will need to provide more details about what you want to do and what the naming format will be etc.

If the file will not have a consistent name/format each week, then how will you identify which file is the correct to import? e.g. will the file have a later modified date than any other?

I have used operating system-level directory listing functions to do things like import the latest file in a directory, or create a libname for the latest data released etc. - that may also be an option for you. If your platform lets you, you could try to pipe commands straight to SAS, or like me you may have to resort to writing a stand alone batch step (which for me, runs each time I log in to my PC, or otherwise whenever I kick it off) -- not ideal for many reasons, but the best I can do in my environment.

This should give you a starting point.
https://blogs.sas.com/content/sgf/2016/03/11/using-a-pipe-to-return-the-output-of-an-operating-syste...

[JonathanV]

Thanks for the replies. Here more info:

*The map (folder) is always the same

*The structure and variables are always the same in csv

*The beginning of the name is always the same, only the end had a time/data stamp which is unique

🙂

[JonathanV]

had = has

[Kurt_Bremser]

Can you use external  commands (XCMD)? If yes, which operating system does your SAS server have?

How is the timestamp in the filename structured?

[JonathanV]

I use Sas EG; the filename is ADRESSES_26MAY2021121204.csv -> the date/time always varietes

[Kurt_Bremser]

NEVER EVER use dates like this in filenames, not even when hell freezes over so hard that the brimstone becomes superconducting.

Always use a YMD order, and no month names, only month numbers, and have leading zeroes for day and month. This will sort the filenames automatically, and retrieving the most recent is a piece of cake.

With a stupid date like you have, you need to first create a dataset of filenames, convert the date and time to a SAS datetime, and then sort, so you can pick the last.

That you use EG is irrelevant, we need to know the operating system of your SAS server and if option XCMD is set. This will determine how the dataset of filenames can be created.

[JonathanV]

Sorry, this aint helping me, I'll try smtng different. Thanks anyways

[SASKiwi]

What about changing your process so that once a CSV is read it is moved to an archive folder? That means you will only have one file in your current folder so no need to figure out which one to process.

[JonathanV]

Yes, its Always 1.. after my process it will manually be archived...

[Kurt_Bremser]

Just to show you what you need to do to get the most recent file with these dates:

first, I created some sample files:

proc export
  data=sashelp.class
  file="/folders/myfolders/ADRESSES_26MAY2021121204.csv"
  dbms=csv
  replace
;
proc export
  data=sashelp.class
  file="/folders/myfolders/ADRESSES_27MAY2021121204.csv"
  dbms=csv
  replace
;
proc export
  data=sashelp.class
  file="/folders/myfolders/ADRESSES_28MAY2021121204.csv"
  dbms=csv
  replace
;

Then, I set up the parameters:

%let path=/folders/myfolders;
%let fname=ADRESSES_;

Next, I used file/directory functions to retrieve the filenames, select files, and extract the timestamp:

data fnames;
length
  fref $8
  fname $200
  datestr $17
;
format fdate e8601dt19.;
rc = filename(fref,"&path.");
did = dopen(fref);
if did ne 0
then do;
  do i = 1 to dnum(did);
    fname = dread(did,i);
    if index(fname,"&fname.")
    then do;
      datestr = scan(scan(fname,-2,"."),-1,"_");
      date = input(substr(datestr,1,9),date9.);
      time = input(substr(datestr,10),hhmmss6.);
      fdate = dhms(date,0,0,time);
      output;
    end;
  end;
  rc = dclose(did);
end;
rc = filename(fref);
keep fname fdate;
run;

To put the most recent first, I sorted in descending order:

proc sort data=fnames;
by descending fdate;
run;

so I could put the first entry into a macrovar:

data _null_;
set fnames (obs=1);
call symputx("fname",fname,"g");
run;

which I then used to import:

proc import
  datafile="&path./&fname."
  dbms=csv
  replace
  out=test
;
run;

But note, that this can be done in a single step if proper timestamps are used and XCMD is active:

data _null_;
infile "ls -r &path./&fname.*.csv" pipe;
length fname $200;
input fname;
call symputx("fname",fname,"g");
stop;
run;

Maxim 33: Intelligent Data Makes for Intelligent Programs.

This is also true for file or directory names.