Special case in retrieving names of all file in one folder

[Phil_NZ]

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.

[PeterClemmensen]

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;

[Phil_NZ]

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!

[PeterClemmensen · Accepted solution]

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;

[Shmuel]

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

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

[Kurt_Bremser]

Do this:

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