topic Re: Remove suffix filename in SAS Programming

Remove suffix filename

AnnLyn — Thu, 14 Nov 2024 17:03:59 GMT

Hi,

we have csv files delivered with name *_<date><id>. We want to easy rename the output files to * without suffix. Eg. class_20241114to class . How is that done in an easy way. We are running Sas 9.4 on Linux. We use Sas data integration studio. But can as well use the filesystem. We do not want a shell script. But one command is fine. Thanks in advance for solutions.

Re: Remove suffix filename

ballardw — Thu, 14 Nov 2024 17:47:05 GMT

Does the file location where these CSV files reside ever have more than one file in the folder with different suffixes?

Such as class_20241114to and class_20241125ab?

If so what name do you want as renaming both to "class" will basically lose one or more of the files involved.

Can you share why you need the suffix removed? It seems possible that you may lose information.

Re: Remove suffix filename

Tom — Thu, 14 Nov 2024 17:58:20 GMT

NO idea about what SAS Integration Studio can or cannot help with. But it is simple with SAS code.

It is not clear what you actually are trying to do but let's just look at one command situation. You receive periodic files all in the same structure and the NAME of the file has a value, say a DATE string, that does not exist as a variable in the file. In that case you can use the FILENAME= option of the INFILE statement to the data step that reads the file create a temporary character variable that has the name of the file. You can then extract the information from there and save it into a real variable.

So a data step like this could read ALL of the CSV files and make a single SAS dataset that now has DATE and ID as separate variables.

data class;
  length id $8 date 8 fname $200;
  format date yymmdd10.;
  infile 'class*.csv' dsd truncover filename=fname;
  input @;
  if fname ne lag(fname) then do;
    date = input(scan(fname,-1,'_').yymmdd8.);
    id = substr(scan(fname,-1,'_.'),8);
    delete;
  end;
  retain date id ;
  length var1 8 var2 $30 varlast 8;
  input (var1 -- varlast) (+0);
run;

Re: Remove suffix filename

ChrisNZ — Thu, 14 Nov 2024 19:19:36 GMT

Like this?

data _null_;
  FILENAME='~/fff_tt';
  RC = rename( FILENAME
             , substr(FILENAME, 1, index(FILENAME, '_')-1)
             , 'file');
  putlog RC=;           
run;

Re: Remove suffix filename

ChrisNZ — Thu, 14 Nov 2024 20:01:45 GMT

One statement would be something like

 %put rc = %sysfunc(rename( &FILENAME
         , %substr(&FILENAME, 1, %index(&FILENAME,_)-1)
         , file));

when the filename is stored in a macro variable.

Re: Remove suffix filename

Patrick — Fri, 15 Nov 2024 00:59:02 GMT

@AnnLyn I assume you want to rename the file to a static name so you can use it with an external file reader transformation.

It's years ago that I've used DIS but if I remember right then once you've created the external file metadata object it's possible to amend it to use a filename with a column modifier (class_:) which then would work for any external file that fits the pattern.

I can't remember if this tweak needs to be done in the external file metadata or the file reader transformation. If you look at the generated code and then play a bit with DIS (=change something and inspect where the generated code changes) you should be able to figure it out.