Concatenating name in macro

[Tom]

I think because "mydir" overthere is assign to a whole folder, not a specific file. Therefore, we open "mydir", we open all the files inside this folder. Then, if we have three files in this folder, dnum(did)=3. Then the codes following will execute multiple files. It is my understanding, aligning with the results.

It helps to understand what a directory actually is in terms of how the file system works.  A directory is actually just a special kind of file. What is stored in the file is the names of the files that exist in that directory along with other metadata about the files.  By opening the fileref MYDIR with the DOPEN() function instead of FOPEN() function you can read the list this metadata about the files. But you not opening the actual individual files themselves. You are just reading the directory itself so you pull out the names of the files that exist in that directory.

[Shmuel · Accepted solution]

1) You can neglect point 1 as I accepted your clarification of ignoring the 2nd %substr.

2) In your first post https://communities.sas.com/t5/SAS-Programming/How-to-replicate-work-for-all-sheets-in-Excel/m-p/708... you added next code as a reply to @Andreas:

*Get filename;
   data filenames;
length fref $8 fname $200;
did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
did = dopen(fref);
do i = 1 to dnum(did);
  fname = dread(did,i);
  output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

I was focusing on the preceding code only and did not noticed details of that part, just copied it as is.

I prefer now to focus on issues you have now, instead of focusing on the history of the code.

3) Relating to FILENAME:

    On a second thought I understand the confusion - mine as yours.

     There are two methods to assign a file reference (FileRef or fref) to a physical file or directory:

    a) A preceding declaration statement. Its syntax is:

FILENAME <fileref> [<engine>] "<path>[<file name>]";

    b) A SAS function, as shown in the documentation you posted the link:

rc = FILENAME(fileref, filename <,device-type> <, 'host-options'>
<, directory-reference>);

   pay attention to the documentation line

fileref
specifies the fileref to assign to the external file. 
In a DATA step, fileref can be a character expression, 
   a string enclosed in single quotation marks that specifies the fileref, or 
   a DATA step variable whose value contains the fileref. 

  rc stands for Return Code. In case of file or directory not found (or other reasons) RC will contain an error code > 0. Zero means assignment was done successfully.

You can use any of next methods, either

filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
   .......

OR 

data _null_;
   rc = filename('mydir','C:\Users\pnguyen\Desktop\New folder');
   did = dopen('mydir');
   do i = 1 to dnum(did);
     .........
   end;
   did = dclose(did);
   rc = filename('mydir'); /* optionl - clears the file refernce, disconnects it from the file/directory */

OR

data _null_;
   fref = 'mydir';
   rc = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
   did = dopen(fref);
   do i = 1 to dnum(did);
     .........
   end;
   did = dclose(did);
   did = filename(fref);

[Phil_NZ]

Awesome, thank you @Shmuel , I think we are about to go to an end

I have some questions relating to the code a bit, I want to talk about the very first option

filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
   .......

1. So, "did" here means that we open the directory, am I correct? Is there anything special that the word"did" stand for?

2. Apart from that, I am wondering why you open the directory 'mydir' but not close afterward?

I mean, should I delete the codeline

did = dclose(did);

in the code below?

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=34);');
  call execute(cmd);
end;
did = dclose(did);
keep fname;
run;

Many thanks!

[Shmuel]

1) did stands for Directory-ID.

    If it was a file (not a directory) I would use fid.

    Both are assigned a value through dopen (fopen) functions.

    Anyhow you can use any valid SAS variable name.

    In both cases it contains a positive sequence number of filenames starting at 1.

    It is reset to zero when closed by dclose or fclose.

    There are some functions to get information from the directory or the file, like dnum you already used.

  
  2) You open a directory by dopen.

      By closing it (by dclose) you release the resources used.