topic Re: Concatenating name in macro in SAS Programming

Concatenating name in macro

Phil_NZ — Wed, 06 Jan 2021 19:12:45 GMT

Hi SAS Users,

I have a code as below, to merge sheets (named sheet1, sheet2, ......) in one file and repeat it through two files in one folder ( Argentina_ and Bulgaria_). My outputs of interest are : "ARG_SHEETMERGE1, ARG_SHEETMERGE2, ARG_SHEETMERGE3, BUL_SHEETMERGE1, BUL_SHEETMERGE2, BUL_SHEETMERGE3" as the picture below:

I am wondering is there any way to adjust the code a little bit to get the result "ARG_MERGE1, ARG_MERGE2, ARG_MERGE3, BUL_MERGE1, BUL_MERGE2, BUL_MERGE3" without affecting other names (ARG_SHEET34_OUTX, ARG_SHEET34_OUT being stayed tune)?

Here is my code


/* Macro for all sheets in one file (from 1 to 34)*/
options compress=yes reuse=yes;

%macro ImportAndTranspose(
      File=
	  , outf=
      , StartSheet=
      , EndSheet=
   );
   
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if &i=1 %then %do;
	
    proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet1$A:X";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;
	%end;

	  %else %if &i= &EndSheet. %then %do;
      proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;

      proc transpose data= &outf.&i. 
            out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=&outf.&i._out;
	  run;

	  data &outf.&i._outx;
	  set &outf.&i._out;
      if s&i. in: ('NA', '$$') then s&i. =".";
	  run;

	  %end;
    
   
    %else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
      proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;

      proc transpose data= &outf.&i. 
            out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=&outf.&i._out;
	  run;

      data &outf.&i._outx;
      set &outf.&i._out;

      if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
	  drop s&i.;
      run;
	  %end;
	%end;
 /* Merging*/
  data &outf.merge1;
     merge
	  %do i =&StartSheet.+1 %to &EndSheet.-1;
	    &outf.&i._outx(keep= type year s&i.2)
	  %end;
	;
	by type year;
  run;

  data &outf.merge2;
     merge
	  &outf.merge1
	  &outf.&EndSheet._out (keep=type year s&EndSheet.)
	  ;
	 by type year;
  run;

 data &outf.merge3;
     merge
	  &outf.&StartSheet.
	  &outf.merge2 
	 ;
	 by type;
 run;

   
   
%mend;

/*Replicate all files in one folder*/

   data _null_;
length fref $8 fname $200;
did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');

/*'fref' stands for File Reference and is limited to 8 chracters.*/
did = dopen(fref);
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);
did = filename(fref);
keep fname;
run;

Many thanks and warmest regards.

Re: Concatenating name in macro

mkeintz — Wed, 06 Jan 2021 19:43:10 GMT

I suppose there is a way to tell sas to cycle through a set of sheets in a workbook, in order of appearance.

But instead of defining a macro with STARTSHEET and ENDSHEET, you could pass two lists to the macro, as in:

%macro exmpl(country_list=,sheetlist=);
  %do c=1 %to %sysfunc(countw(&country_list));
	%do s=1 %to %sysfunc(countw(&sheetlist));
	  %let sheetname=%scan(&country_list,&c)_%scan(&sheetlist,&s);

      /** Sheet-specific code here **/


	%end;
  %end;
%mend;
%exmpl(country_list=arg bul,sheetlist=sheetmerge1 sheetmerge2 sheetmerge3);

One of the benefits of this approach is that you don't have to rely on the order the sheets appear in the workbook. Or course, this relies on having a two-level sheet naming convention.

Re: Concatenating name in macro

Shmuel — Wed, 06 Jan 2021 21:02:44 GMT

You are getting names like ARG_SHEETMERGn because &outf is assigned as

outf=',short_fn,'sheet

I suggest to correct the &outf value for the MERGING part:

/* Merging*/
 %let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-10)); /* new line */
  data &outm.merge1;    /* line changed */
     merge
	  %do i =&StartSheet.+1 %to &EndSheet.-1;
	    &outf.&i._outx(keep= type year s&i.2)
	  %end;
	;
	by type year;
  run;

  data &outfm.merge2;    /* line changed */
     merge
	  &outfm.merge1    /* line changed */
	  &outf.&EndSheet._out (keep=type year s&EndSheet.)
	  ;
	 by type year;
  run;

 data &outfm.merge3;     /* line changed */
     merge
	  &outf.&StartSheet.
	  &outfm.merge2    /* line changed */
	 ;
	 by type;
 run;

Re: Concatenating name in macro

Phil_NZ — Wed, 06 Jan 2021 21:36:39 GMT

Hi @Shmuel ! Thank you very much for your solution, it goes directly to the problem!

I haven't tested yet but to me, whether we need this line of code?

%substr(&outf,10,%eval(%length(&outf)-10));

Can you please let me know why we need this code, many thanks!

Re: Concatenating name in macro

Shmuel — Wed, 06 Jan 2021 22:06:41 GMT

Only the merged datasets names contain the substring sheetmerge.

The goal is to cut out the sheet of that substring.

First merge - only the merge output need correction.

The 2nd and 3rd merge need correction for the output and for one of the inputs.

So I preferred to create a new macro variable &outfm:

%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-10));

First part %substr(&outf,1,4) contains for example ARG_ (4 characters).

The 2nd part need skip the SHEET part to concatenate the final part which length depends

on sheet number (one or two digits) - it starts at position 10 and the rest length is calculated.

Re: Concatenating name in macro

Phil_NZ — Thu, 07 Jan 2021 02:06:01 GMT

Hi @Shmuel

Thank you very much for your replies and explanations, but there are some other things that happened when running this code:

So, the code is:

 %let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-10));
  data &outfm.merge1;
     merge
	  %do i =&StartSheet.+1 %to &EndSheet.;
	    &outf.&i._outx(keep= type year s&i.)
	  %end;
	;
	by type year;
  run;


 data &outfm.merge2;
     merge
	  &outf.&StartSheet.
	  &outfm.merge1 
	 ;
	 by type;
 run;

1. When I run this code, there are four warnings (compared to 0 warning previously)

WARNING: Argument 2 to macro function %SUBSTR is out of range.
WARNING: Argument 3 to macro function %SUBSTR is out of range.
WARNING: Argument 2 to macro function %SUBSTR is out of range.
WARNING: Argument 3 to macro function %SUBSTR is out of range.

2. Why we need to use %eval in this case, I did a lot of searches but can not find any document that explains the usage of %eval in macro that match your example. I am wondering if you can give me a quick description why we use the eval in the code below:

%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-10));

3. Also the line of code above. From my understanding, you created a macro variable named outfm which is concatenated by two strings: "%substr(&outf,1,4)" and "%substr(&outf,10,%eval(%length(&outf)-10))"

With the first string, let's say ARG_, at the second string, we skip the part "sheet1" or "sheet11" from outf. So, why don't we just use only the first part "%substr(&outf,1,4)" (meaning that if outf is ARG_SHEET, we only get ARG_ and ignore the rest) and ignore the second part because the names of the merged file (&outfm.merge1, &outfm.merge2) no longer relates to outf?

Many thanks and warmest regards.

Re: Concatenating name in macro

Phil_NZ — Fri, 08 Jan 2021 05:34:27 GMT

Hi @Shmuel

I replace your code

%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-10));

%let outfm = %substr(&outf,1,4);

And the result seems to be the same (without WARNING)

I am not sure whether my change is acceptable? or deleting the part "%substr(&outf,10,%eval(%length(&outf)-10))" will affect any other part of the macro ?

Many thanks and regards!

Re: Concatenating name in macro

Shmuel — Fri, 08 Jan 2021 06:30:46 GMT

1) Relating to next message:

WARNING: Argument 2 to macro function %SUBSTR is out of range.

correct line (starting substring at position 10 means need subtract 9 from length)

%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-9));

2) You asked to change from ARG_SHEETMERGEn to ARG_MERGEn.

Pay attention:

%substr(&outf,1,4) contribute the ARG_ substring, while
%substr(&outf,10,%eval(%length(&outf)-9)); contribute the MERGEn. substring.

3) The usage of %eval is to calculate the 2nd argument to the %substr function,

otherwise the generated code will be erroneous

%substr(&outf,10,15-9)); /* ARG_SHEETMERGE1 - is length 15 */

instead the correct code:

%substr(&outf,10,6);   /* length of MERGE1 */

4) Result of above - if you omit the 2nd %substr, you will get dataset name as ARG_ only, and the 2nd merge will replace the output of the 1st merge etc.

and next code will result in errors.

Re: Concatenating name in macro

Phil_NZ — Fri, 08 Jan 2021 09:10:02 GMT

Hi @Shmuel !

I really appreciate your detailed explanation, and I learn a lot from that; however, I hope that we can make it clear a little bit then.

1. Even when I change from 10 to 9 as the code you mentioned, such warnings still exist.

So, I try to point out what really happended.

2. Let's have a look back about the whole code in general:

%macro ImportAndTranspose(
      File=
	  , outf=
      , StartSheet=
      , EndSheet=
   );
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if &i=1 %then %do;
	
    proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet1$A:X";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;
	%end;

	  %else %if &i= &EndSheet. %then %do;
      proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;

      proc transpose data= &outf.&i. 
            out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=&outf.&i._out;
	  run;

	  data &outf.&i._outx;
	  set &outf.&i._out;
      if s&i. in: ('NA', '$$', '') then s&i. =".";/********/
	  run;

	  %end;
    
   
    %else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
      proc import datafile= "&File." 
                  out= &outf.&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= &outf.&i.;
      by Type;
      run;

      proc transpose data= &outf.&i. 
            out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=&outf.&i._out;
	  run;

      data &outf.&i._outx;
      set &outf.&i._out;

      if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
	  drop s&i.;
	  rename s&i.2=s&i.;
      run;
	  %end;
	%end;
 /* Merging*/
%let outfm = %substr(&outf,1,4);
  data &outfm.merge1;
     merge
	  %do i =&StartSheet.+1 %to &EndSheet.;
	    &outf.&i._outx(keep= type year s&i.)
	  %end;
	;
	by type year;
  run;


 data &outfm.merge2;
     merge
	  &outf.&StartSheet.
	  &outfm.merge1 
	 ;
	 by type;
 run;

   
   
%mend;

/*Replicate all files in one folder*/

   data _null_;
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);
  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);
did = filename(fref);
keep fname;
run;

So, for example, the filename( fref) is Argentina_, then fname= Argentina_, then short_fn=Arg_, then outf=Arg_sheet

Therefore, when talking about the code under controversial:

if the code is

%let outfm = %substr(&outf,1,4)

then outfm=Arg_, then when merging, I will have two files Arg_merge1 and Arg_merge2 (because the data step is &outfm.merge1 and &outfm.merge2).

On the other hand, if we follow the code is

%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-9));

At that time, length(outf)=length(Arg_sheet)=9, and then, the 2nd %substr become redundant.

I am wondering but I think the way I present the code confuse you. I think outf in the code

%let outfm = %substr(&outf,1,4)%substr(&outf,10,%eval(%length(&outf)-9));

should be Arg_sheet rather than Arg_sheetmerge1.

I hope that I can receive your idea about that.

Many thanks and warmest regards!

Re: Concatenating name in macro

Shmuel — Fri, 08 Jan 2021 11:29:09 GMT

1) Your statement "Even when I change from 10 to 9 as the code you mentioned, such warnings still exist." - is not in the code you last posted:

/* Merging*/ %let outfm = %substr(&outf,1,4);

To examine the right situation I need you to run the corrected code with OPTIONS MPRINT and post

that part of the log that contains the generated code and the messages relating to it .

2) Your code contains next lines:

/*Replicate all files in one folder*/

   data _null_;
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);
  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);
did = filename(fref);
keep fname;
run;

This is not the code I posted to you. It seems that you made some mismatch between two diffrent posts you got. Pay attention to next two consecutive lines:

did = filename(fref,'C:\.....');

did = dopen(fref);

The first line is erroneous. anyway, the did will be replaced with the result of the 2nd line.

3) The syntax of FILENAME statement is:

filename <fref> [<engine>] "<path and file name>";

In your case I suggest to replace the starting lines of your code with:

filename mydir 'C:\Users\pnguyen\Desktop\New folder');
/* here fref is 'mydir' */
data _null_;
   did = dopen('mydir');
 ....

4) If you code

%let outfm = %substr(&outf,1,4);

you can use - as you suggest - for merging: &outfm.MERG1 or &outfm.MERGE2 etc.

I admit it makes logic and shortens the code.

Re: Concatenating name in macro

Phil_NZ — Fri, 08 Jan 2021 20:24:09 GMT

Hi @Shmuel .Thank you very much for your dedicated helps. Following your guide, I have some replies here

1. When I run OPTIONS MPRINT with the corrected code

%let outfm = %substr(&outf,1,4);

The associated log is

93        +
data Arg_merge1;      merge      Arg_sheet2_outx(keep= type year s2)      Arg_sheet3_outx(keep= type year s3
94        +)      Arg_sheet4_outx(keep= type year s4)      Arg_sheet5_outx(keep= type year s5)      Arg_sheet6_outx(keep= type year 
s6)      Arg_sheet7_outx(keep= type year s7)      Arg_sheet8_outx(keep= type year s8)      Arg_sheet9_outx(keep= type year s9)
95        + Arg_sheet10_outx(keep= type year s10)      Arg_sheet11_outx(keep= type year s11)      Arg_sheet12_outx(keep= type year 
s12)      Arg_sheet13_outx(keep= type year s13)      Arg_sheet14_outx(keep= type year s14)      Arg_sheet15_outx(keep= type year 
s15)
96        +    Arg_sheet16_outx(keep= type year s16)      Arg_sheet17_outx(keep= type year s17)      Arg_sheet18_outx(keep= type 
year s18)      Arg_sheet19_outx(keep= type year s19)      Arg_sheet20_outx(keep= type year s20)      Arg_sheet21_outx(keep= type 
year s21
97        +)      Arg_sheet22_outx(keep= type year s22)      Arg_sheet23_outx(keep= type year s23)      Arg_sheet24_outx(keep= type 
year s24)      Arg_sheet25_outx(keep= type year s25)      Arg_sheet26_outx(keep= type year s26)      Arg_sheet27_outx(keep= type 
year
98        + s27)      Arg_sheet28_outx(keep= type year s28)      Arg_sheet29_outx(keep= type year s29)      Arg_sheet30_outx(keep= 
type year s30)      Arg_sheet31_outx(keep= type year s31)      Arg_sheet32_outx(keep= type year s32)      Arg_sheet33_outx(keep= 
type
99        + year s33)      Arg_sheet34_outx(keep= type year s34)  ;  by type year;   run;

NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET2_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET3_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET4_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET5_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET6_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET7_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET8_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET9_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET10_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET11_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET12_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET13_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET14_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET15_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET16_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET17_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET18_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET19_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET20_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET21_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET22_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET23_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET24_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET25_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET26_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET27_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET28_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET29_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET30_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET31_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET32_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET33_OUTX.
NOTE: There were 14496 observations read from the data set WORK.ARG_SHEET34_OUTX.
NOTE: The data set WORK.ARG_MERGE1 has 14496 observations and 35 variables.
NOTE: Compressing data set WORK.ARG_MERGE1 decreased size by 38.89 percent. 
      Compressed is 44 pages; un-compressed would require 72 pages.
NOTE: DATA statement used (Total process time):
      real time           0.25 seconds
      cpu time            0.21 seconds
      

99        +                                                                                  data Arg_merge2;      merge    
Arg_sheet1    Arg_merge1    ;   by type;  run;

Please let me know unless it is the LOG you mentioned 😄

2. Regarding the second point, I think it was what you suggest to me from the previous topic, maybe I did not get your idea thoroughly last time or the way I present my data confused you, but if it is the case, could you please tell me why you prefer the new version rather than from the previous post (https://communities.sas.com/t5/SAS-Programming/How-to-replicate-work-for-all-sheets-in-Excel/m-p/708535#M217766)?

You said this version has problems with the function filename but as I saw from the document (https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=n15scht124hr4nn1g296cqg2kqfa.htm&locale=en), there seems to be nothing wrong with the code itself per se. So let's say in the last version, you suggest me to use filename as a function and now we use filename as a statement. And I value both approaches but I am not sure why you said that the previous one is wrong (filename as a function)

Yeah, and I also check the new code you suggested

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

And yeah, thank you @Shmuel , it works well without any error.

And regarding the code right above, I think I also can delete the line of code

did = filename(fref);

without causing any error? am I correct about that? (I ran the full code without this line of code and the result seems not to be changed, I am just not sure if this exclusion causes any hidden error that I am incapable to spot out)

To me, between the two versions, I prefer the new version because it is straightforward.

3. Thank you @Shmuel , thank to you and other SAS helpers, I can learn and improve a lot. Many thanks again!

Re: Concatenating name in macro

Tom — Fri, 08 Jan 2021 20:53:39 GMT

Using the FILENAME statement to define the fileref is fine. The main reasons to use the FILENAME() function would be if you had a list of files that you want to process instead of just the one file (directory actually) in your example. Also with the FILENAME() function your code could capture the return code and perhaps handle errors or at least generate a more meaningful message when the fileref cannot be created.

Similarly not clearing the fileref should not matter in your case. Again if you are processing a lot of files then it is more important to clear the filerefs you are no longer using if for no other reason that to remove clutter from your SAS environment.

Re: Concatenating name in macro

Phil_NZ — Fri, 08 Jan 2021 21:09:51 GMT

Hi @Tom

Thank you for your insightful idea and discussion!. However, the filename statement also can work with a list of files (at least I tested with two files in one folder and it worked).

Apart from that, can I ask whether fref is the abbreviation of fileref, because I did quite a bit searches and the word "fref" is not really popularly-used.

And, regarding "fref" clearance, because when I use the code above, I do not contain fref so I just exclude this code, please correct me if I get your idea mistakenly.

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=34);');
  call execute(cmd);
end;
did = dclose(did);
/*did = filename(fref);*/     /* I delete this line of code because fref does not exist above*/
keep fname;
run;

Or you mean, in my case, I need to write the associated code below?

did = filename(mydir);

Many thanks and best regards!

Re: Concatenating name in macro

Tom — Fri, 08 Jan 2021 21:33:23 GMT

A fileref is the name you assigned that points the directory you are trying to scan for files. In your program you are using MYDIR as your fileref. In your program you can use a FILENAME statement after the data step to clear the MYDIR fileref:

filename mydir ;

FREF was the name of a variable in a data step. Clearing the value of the variable does nothing meaningful in a data step that is not writing out any observations.

I have no idea where you are seeing multiples files. Your current program is only pointing MYDIR at a single filename (directory). You can reuse the same fileref over and over to point to multiple files, but only one at a time.

filename mydir 'C:\Users\pnguyen\Desktop\New folder';
filename mydir 'C:\Users\pnguyen\Desktop\Another folder';

And the posted program is not doing that anyway.

You can also point a single filref at multiple directories,

filename mydir
  ( 'C:\Users\pnguyen\Desktop\New folder'
    'C:\Users\pnguyen\Desktop\Another folder'
  )
;

but I don't think that would work well in this program where you are trying to use the DOPEN() and DREAD() functions to list the files in the directory that the fileref points to.

Re: Concatenating name in macro

Shmuel — Fri, 08 Jan 2021 22:09:40 GMT

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/708535#M217766 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);
   .......

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 */

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);

Re: Concatenating name in macro

Phil_NZ — Sat, 09 Jan 2021 03:53:54 GMT

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!

Re: Concatenating name in macro

Phil_NZ — Sat, 09 Jan 2021 04:02:50 GMT

Hi @Tom ,

From my focal point, with the code 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=34);');
  call execute(cmd);
end;
did = dclose(did);
keep fname;
run;

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.

I am not sure whether I fell into any fallacy.

Many thanks and best regards!

Re: Concatenating name in macro

Shmuel — Sat, 09 Jan 2021 06:56:23 GMT

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.

Re: Concatenating name in macro

Tom — Sat, 09 Jan 2021 19:19:35 GMT

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.