topic Re: Special case in retrieving names of all file in one folder in SAS Programming

Special case in retrieving names of all file in one folder

Phil_NZ — Mon, 25 Jan 2021 05:21:56 GMT

Hi SAS Users,

I have a problem when trying to run the macro ImportAndTranspose for all files in one folder (named New Folder). The filename is the name of countries with an under space, for example: Argentina_, Austria_, Australia_, Belgium_, US_, UK_,..... and I have 64 files like that.

The code is as below:

filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
  fname = dread(did,i);
  short_fn= cats(substr(fname, 1,3),'_');
  cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
      strip(fname),',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');
  call execute(cmd);
end;
keep fname;
run;

Now I just recognize that if I do

short_fn= cats(substr(fname, 1,3),'_');

So, the short_fn for Australia and Austria or Slovakia and Slovenia are the same, but if I did

short_fn= cats(substr(fname, 1,4),'_');

UK_ and US_ do not have enough character in their names, and it make make the results' name in general not aesthetic.

Can you please give me any suggestion to adjust the code to solve the problem?

Many thanks and warm regards.

Re: Special case in retrieving names of all file in one folder

PeterClemmensen — Mon, 25 Jan 2021 05:42:46 GMT

Try something like this

data have;
input fname $10.;
datalines;
Australia 
Austria   
Slovakia  
UK        
US        
;

data want;
   set have;
   short_fn = cats(substr(fname, 1, min(3, length(fname))), '_');
run;

Re: Special case in retrieving names of all file in one folder

Phil_NZ — Mon, 25 Jan 2021 05:55:02 GMT

Hi @PeterClemmensen

The result is as below:

I mean, one reasonable solution is to get Austra_ for Australia, Austri_ for Austria, UK_ for the UK, US_ for the US, and the other files are normal for example: ARG_ for Argentina, CHI_ for China, so on and so forth.

Many thanks!

Re: Special case in retrieving names of all file in one folder

PeterClemmensen — Mon, 25 Jan 2021 06:22:19 GMT

Ok. Obviously, you can do this with simple if-then logic. I will spare you the details.

For maintainability and length control, this is a good case using user-defined Function Logic in Proc Format. Here, I simply make 'special' cases for some countries, while the rest is sent to the function f, which does the work. Then all that is needed is a Put Function in a data step.

Feel free to ask 🙂

data have;
input fname $10.;
datalines;
Australia 
Austria   
Slovakia  
UK        
US        
;

proc fcmp outlib=work.f.f;
   function f(s $) $;
      length r $ 4;
      r = upcase(cats(substr(s, 1, 3), '_'));
      return (r);
   endsub;
run;

options cmplib = work.f;
proc format;
   value $ country (default = 12)
      'Australia' = 'Austra_'
      'Austria'   = 'Austri_'
      'UK'        = 'UK_'
      'US'        = 'US_'
      other       = [f()]
   ;
run;

data want;
   set have;
   short_fn = put(fname, $country.);
run;

Re: Special case in retrieving names of all file in one folder

Shmuel — Mon, 25 Jan 2021 06:38:01 GMT

Replace the substr() function into scan() function:

short_fn = cats(scan(fname,1,'_'),'_');

Re: Special case in retrieving names of all file in one folder

Kurt_Bremser — Mon, 25 Jan 2021 08:47:34 GMT

Do this:

length shortfn $4;
shortfn = scan(fname,1,"_");