topic Re: renaming multiple pc files before importing in SAS Programming

renaming multiple pc files before importing

veerand — Fri, 05 Jun 2020 07:57:05 GMT

I am using EG which is running on GRID and I cant use PIPE.
I have to import monthly 10 txt files and manipulate them before exporting to CAS library for VA

Files are named from source:
HR002_Puudumised ja osalemised_2020-05-03-07-05-27.txt
HR009_Teenistusse tööle võetud_2020-05-03-07-20-29.txt
HR001_Isikkoosseisu nimekiri_2020-05-03-07-00-26.txt
HR025_Tekkepõhine palgakulu kontolaiendite lõikes_2020-05-10-07-40-04.txt
HR003_Staazid_2020-05-03-07-10-28.txt
HRxxx_Palga orienteeruv kokkuhoid_2020-05-03-07-45-35.txt
HR010_Lahkumised_2020-05-03-07-25-30.txt
HR014_Määratud palga aruanne_2020-05-03-07-30-31.txt
HR021_Koosseisuaruanne_2020-05-03-07-35-01.txt
HR005_Vakantsed ametikohad_2020-05-03-07-15-29.txt

simple renaming works
data _null_;
rc=rename('/storage/sasdata/data03/users/andres.veer/HR009_Teenistusse tööle võetud_2020-05-03-07-20-29.txt', '/storage/sasdata/data03/users/andres.veer/HR009.txt', 'file');
run;

So i need help how to rename those original long names just to shorter names as HR009.txt

Re: renaming multiple pc files before importing

andreas_lds — Fri, 05 Jun 2020 08:57:49 GMT

If you can't use pipe, then you have to use the functions dopen, dread etc. in a data-step.

Re: renaming multiple pc files before importing

Oligolas — Fri, 05 Jun 2020 09:11:42 GMT

Hi,

maybe this will work for you:

%macro list_files(dir,ext);
  %local filrf rc did memcnt name i;
  %let rc=%sysfunc(filename(filrf,&dir));
  %let did=%sysfunc(dopen(&filrf));      

   %if &did eq 0 %then %do; 
    %put Directory &dir cannot be open or does not exist;
    %return;
  %end;

   %do i = 1 %to %sysfunc(dnum(&did));   

   %let name=%qsysfunc(dread(&did,&i));

      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do;
        %put &dir\&name;
         data _null_;
             rc = rename("&dir\&name", "&dir\%scan(&name.,1,_).&ext", 'file');
         run;

      %end;
      %else %if %qscan(&name,2,.) = %then %do;        
        %list_files(&dir\&name,&ext)
      %end;

   %end;
   %let rc=%sysfunc(dclose(&did));
   %let rc=%sysfunc(filename(filrf));     

%mend list_files;
options mprint;
%list_files(/storage/sasdata/data03/users/andres.veer/,txt);

Re: renaming multiple pc files before importing

Eigth_Below — Fri, 05 Jun 2020 09:47:17 GMT

Here is my code. I hope this could help you.

%macro rename_files(path=);
data a;
r=filename("fir","&path.");
d=dopen("fir");
if d>0 then do;
n=dnum(d);
do i=1 to n;
fname=dread(d,i);
if find(fname,".txt")>0 then do;
output;
end;
end;
end;
c=dclose(d);
keep fname;
run;

proc sql noprint;
select count(*) into :n
from a
;
select fname into: fnames separated by "*"
from a
;
quit;

%do i=1 %to &n.;
%let fname=%scan(&fnames.,&i.,*);
%let new_fname=%scan(&fname,1,_);

%put &fname.;
data _null_;
rc=rename("&path.\&fname.","&path.\&new_fname..txt", 'file');
run;

%end;

%mend rename_files;

Re: renaming multiple pc files before importing

veerand — Fri, 05 Jun 2020 10:00:04 GMT

Thank You very much Oligolas, it worked after minor replacements "\" to "/" as my GRID is on unix. Now reading your code, it is so obvious that I am mad that in two weeks I was not able to write it myself

Re: renaming multiple pc files before importing

RichardDeVen — Fri, 05 Jun 2020 11:56:41 GMT

Your server session may be in a lockdown state per administrative policies. If so, the RENAME function may not work out of the box.

From RENAME Docs:

If the SAS session in which you are specifying the FILEEXIST function is in a locked-down state, and the pathname specified in the function has not been added to the lockdown path list, then the function will fail and a file access error related to the locked-down data will not be generated in the SAS log unless you specify the SYSMSG function.

Re: renaming multiple pc files before importing

ballardw — Fri, 05 Jun 2020 15:09:29 GMT

I don't do much with EG and we don't have GRID, CAS or VA at all. I am not sure why you need to shorten the names. If long file names are the issue I would bring this to SAS as a tech support issue as I would expect to be able to read any valid file name.

Or is the issue that you have multiple files that start the same an have the date as a difference in the name and just want a shorter "name" to use elsewhere in code that doesn't need to change?