"ERROR: "WORK." is not a valid name." when using a nested macro variable in macro program

[leehsin]

Hi, I couldn't understand why I got errors after running the following code. The errors can be replicated by running this sample code in your SAS environment.

/* The data importing macro I used */
%macro CsvExl_Import(location=, filename=, DBMS=, outfile=);
    proc import datafile="&location./&filename..&DBMS."
                DBMS= &DBMS.
				REPLACE
                out= work.&outfile.;
                getnames=yes;
                guessingrows=200000;
    run;
%mend CsvExl_Import;

/* The global macro variable existed in my environment */
%let deployMode = "dev";

/*##################################*/
/* create sample data sets for demo */
data one;                     
   input IDnumber $ Week1 Week16;
   WeightLoss2=Week1-Week16;     
   datalines;           
2477 195  163
2431 220  198
2456 173  155
2412 135  116
;

data two;                     
   input IDnumber $ Week1 Week16;
   WeightLoss2=Week1-Week16;     
   datalines;           
4321 321  768
3245 342  754
1324 123  546
6237 543  121
;
/* Write to csv files in a designed folder with the pattern I want */
proc export data=work.one
    outfile='C:/cctem/2020q3/dev/dev_sample_2019.csv'
    dbms=csv
    replace;
run;

proc export data=work.two
    outfile='C:/cctem/2020q3/dev/dev_sample_2020.csv'
    dbms=csv
    replace;
run;

/*##################################*/
/* I need to use the following macro variables in my programs */
%let env = %qsysfunc(compress(&deployMode.,%str(%")));
%let years = 19 20;
%let impl = 2020q3;
%put &env;

/* Below is the code I need to use and got the error that I am asking */
%macro build_table();
    %local i;

    %do i = 1 %to %sysfunc(countw(&years));
		/* Define values for macro variables of Year(s) and File name(s)*/
		%let years&i = %scan(&years, &i);
		%let File_&i = &env._sample_20&&years&i;

		data three;
		set sashelp.class;
		run;

		/* Read data of each year */
		%CsvExl_Import(location= C:/cctem/&impl./&env.
            			,filename= &&File_&i.
		    			,DBMS= csv
            			,outfile= &&File_&i.);

	%end;

%mend;

/* Run macro program */
%build_table();

The issue came from the %CsvExl_Import() inside the %macro build_table(). Here are the scenarios:

1) If I remove %CsvExl_Import() , no error.

2) If I replace 

%let env = %qsysfunc(compress(&deployMode.,%str(%")));

with 

%let env = dev;

then no error.

But, "%let env = %qsysfunc(compress(&deployMode.,%str(%")));" is the way I want to use in my program. I want to assign macro variable &env from a macro variable defined in a configuration program in the system. I believe something related to the macro variable that I didn't do it in the right way, and reach out for the help. Thanks a lot !

[PaigeMiller]

Hi, I couldn't understand why I got errors after running the following code.

Run the code again with the command options mprint; as the first line. Please show us the log. Paste the log as text into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

[leehsin]

Thanks for the advice. I re-run the code with the first line "options mprint;". Here is the new code:

options mprint;

%macro CsvExl_Import(location=, filename=, DBMS=, outfile=);
    proc import datafile="&location./&filename..&DBMS."
                DBMS= &DBMS.
				REPLACE
                out= work.&outfile.;
                getnames=yes;
                guessingrows=200000;
    run;
%mend CsvExl_Import;

%let deployMode = "dev";

/*##################################*/
/* create data sets */
data one;                     
   input IDnumber $ Week1 Week16;
   WeightLoss2=Week1-Week16;     
   datalines;           
2477 195  163
2431 220  198
2456 173  155
2412 135  116
;

data two;                     
   input IDnumber $ Week1 Week16;
   WeightLoss2=Week1-Week16;     
   datalines;           
4321 321  768
3245 342  754
1324 123  546
6237 543  121
;
/* Write to csv files */

proc export data=work.one
    outfile='C:/cctem/2020q3/dev/dev_sample_2019.csv'
    dbms=csv
    replace;
run;

proc export data=work.two
    outfile='C:/cctem/2020q3/dev/dev_sample_2020.csv'
    dbms=csv
    replace;
run;

/*##################################*/

%let env = %qsysfunc(compress(&deployMode.,%str(%")));
%let years = 19 20;
%let impl = 2020q3;
%put &env;



%macro build_table();
    %local i;

    %do i = 1 %to %sysfunc(countw(&years));
		/* Define values for macro variables of Year(s) and File name(s)*/
		%let years&i = %scan(&years, &i);
		%let File_&i = &env._sample_20&&years&i;

		data three;
		set sashelp.class;
		run;

		/* Read data of each year */
		%CsvExl_Import(location= C:/cctem/&impl./&env.
            			,filename= &&File_&i.
		    			,DBMS= csv
            			,outfile= &&File_&i.);

	%end;

%mend;

/* Run macro program */
%build_table();

Here is the log:

509  options mprint;
510
511  %macro CsvExl_Import(location=, filename=, DBMS=, outfile=);
512      proc import datafile="&location./&filename..&DBMS."
513                  DBMS= &DBMS.
514                  REPLACE
515                  out= work.&outfile.;
516                  getnames=yes;
517                  guessingrows=200000;
518      run;
519  %mend CsvExl_Import;
520
521  %let deployMode = "dev";
522
523  /*##################################*/
524  /* create data sets */
525  data one;
526     input IDnumber $ Week1 Week16;
527     WeightLoss2=Week1-Week16;
528     datalines;

NOTE: The data set WORK.ONE has 4 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


533  ;
534
535  data two;
536     input IDnumber $ Week1 Week16;
537     WeightLoss2=Week1-Week16;
538     datalines;

NOTE: The data set WORK.TWO has 4 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


543  ;
544  /* Write to csv files */
545
546  proc export data=work.one
547      outfile='C:/cctem/2020q3/dev/dev_sample_2019.csv'
548      dbms=csv
549      replace;
550  run;

551   /**********************************************************************
552   *   PRODUCT:   SAS
553   *   VERSION:   9.4
554   *   CREATOR:   External File Interface
555   *   DATE:      17SEP20
556   *   DESC:      Generated SAS Datastep Code
557   *   TEMPLATE SOURCE:  (None Specified.)
558   ***********************************************************************/
559      data _null_;
560      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
561      %let _EFIREC_ = 0;     /* clear export record count macro variable */
562      file 'C:/cctem/2020q3/dev/dev_sample_2019.csv' delimiter=',' DSD DROPOVER lrecl=32767;
563      if _n_ = 1 then        /* write column names or labels */
564       do;
565         put
566            "IDnumber"
567         ','
568            "Week1"
569         ','
570            "Week16"
571         ','
572            "WeightLoss2"
573         ;
574       end;
575     set  WORK.ONE   end=EFIEOD;
576         format IDnumber $8. ;
577         format Week1 best12. ;
578         format Week16 best12. ;
579         format WeightLoss2 best12. ;
580       do;
581         EFIOUT + 1;
582         put IDnumber $ @;
583         put Week1 @;
584         put Week16 @;
585         put WeightLoss2 ;
586         ;
587       end;
588      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
589      if EFIEOD then call symputx('_EFIREC_',EFIOUT);
590      run;

NOTE: The file 'C:/cctem/2020q3/dev/dev_sample_2019.csv' is:
      Filename=C:\cctem\2020q3\dev\dev_sample_2019.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=17Sep2020:11:51:40,
      Create Time=17Sep2020:10:50:30

NOTE: 5 records were written to the file 'C:/cctem/2020q3/dev/dev_sample_2019.csv'.
      The minimum record length was 15.
      The maximum record length was 33.
NOTE: There were 4 observations read from the data set WORK.ONE.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


4 records created in C:/cctem/2020q3/dev/dev_sample_2019.csv from WORK.ONE.


NOTE: "C:/cctem/2020q3/dev/dev_sample_2019.csv" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.23 seconds
      cpu time            0.12 seconds


591
592  proc export data=work.two
593      outfile='C:/cctem/2020q3/dev/dev_sample_2020.csv'
594      dbms=csv
595      replace;
596  run;

597   /**********************************************************************
598   *   PRODUCT:   SAS
599   *   VERSION:   9.4
600   *   CREATOR:   External File Interface
601   *   DATE:      17SEP20
602   *   DESC:      Generated SAS Datastep Code
603   *   TEMPLATE SOURCE:  (None Specified.)
604   ***********************************************************************/
605      data _null_;
606      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
607      %let _EFIREC_ = 0;     /* clear export record count macro variable */
608      file 'C:/cctem/2020q3/dev/dev_sample_2020.csv' delimiter=',' DSD DROPOVER lrecl=32767;
609      if _n_ = 1 then        /* write column names or labels */
610       do;
611         put
612            "IDnumber"
613         ','
614            "Week1"
615         ','
616            "Week16"
617         ','
618            "WeightLoss2"
619         ;
620       end;
621     set  WORK.TWO   end=EFIEOD;
622         format IDnumber $8. ;
623         format Week1 best12. ;
624         format Week16 best12. ;
625         format WeightLoss2 best12. ;
626       do;
627         EFIOUT + 1;
628         put IDnumber $ @;
629         put Week1 @;
630         put Week16 @;
631         put WeightLoss2 ;
632         ;
633       end;
634      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
635      if EFIEOD then call symputx('_EFIREC_',EFIOUT);
636      run;

NOTE: The file 'C:/cctem/2020q3/dev/dev_sample_2020.csv' is:
      Filename=C:\cctem\2020q3\dev\dev_sample_2020.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=17Sep2020:11:51:41,
      Create Time=17Sep2020:10:50:30

NOTE: 5 records were written to the file 'C:/cctem/2020q3/dev/dev_sample_2020.csv'.
      The minimum record length was 16.
      The maximum record length was 33.
NOTE: There were 4 observations read from the data set WORK.TWO.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


4 records created in C:/cctem/2020q3/dev/dev_sample_2020.csv from WORK.TWO.


NOTE: "C:/cctem/2020q3/dev/dev_sample_2020.csv" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.21 seconds
      cpu time            0.11 seconds


637
638  /*##################################*/
639
640  %let env = %qsysfunc(compress(&deployMode.,%str(%")));
641  %let years = 19 20;
642  %let impl = 2020q3;
643  %put &env;
dev
644
645
646
647  %macro build_table();
648      %local i;
649
650      %do i = 1 %to %sysfunc(countw(&years));
651          /* Define values for macro variables of Year(s) and File name(s)*/
652          %let years&i = %scan(&years, &i);
653          %let File_&i = &env._sample_20&&years&i;
654
655          data three;
656          set sashelp.class;
657          run;
658
659          /* Read data of each year */
660          %CsvExl_Import(location= C:/cctem/&impl./&env.
661                          ,filename= &&File_&i.
662                          ,DBMS= csv
663                          ,outfile= &&File_&i.);
664
665      %end;
666
667  %mend;
668
669  /* Run macro program */
670  %build_table();
MPRINT(BUILD_TABLE):   data three;
MPRINT(BUILD_TABLE):   set sashelp.class;
MPRINT(BUILD_TABLE):   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.THREE has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


ERROR: "WORK." is not a valid name.
NOTE 138-205: Line generated by the macro variable "OUTFILE".
1     work.dev_sample_2019
              ------------
              22
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

NOTE: Line generated by the macro variable "OUTFILE".
1     work.dev_sample_2019
              ------------
              202
ERROR 202-322: The option or parameter is not recognized and will be ignored.

NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
MPRINT(CSVEXL_IMPORT):   proc import datafile="C:/cctem/2020q3/dev/dev_sample_2019.csv" DBMS= csv
REPLACE out= work.dev_sample_2019 getnames=yes;
MPRINT(CSVEXL_IMPORT):   guessingrows=200000;
MPRINT(CSVEXL_IMPORT):   run;
MPRINT(BUILD_TABLE):  ;


MPRINT(BUILD_TABLE):   data three;
MPRINT(BUILD_TABLE):   set sashelp.class;
MPRINT(BUILD_TABLE):   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.THREE has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


ERROR: "WORK." is not a valid name.
NOTE 138-205: Line generated by the macro variable "OUTFILE".
1     work.dev_sample_2020
              ------------
              22
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

NOTE: Line generated by the macro variable "OUTFILE".
1     work.dev_sample_2020
              ------------
              202
ERROR 202-322: The option or parameter is not recognized and will be ignored.

NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
MPRINT(CSVEXL_IMPORT):   proc import datafile="C:/cctem/2020q3/dev/dev_sample_2020.csv" DBMS= csv
REPLACE out= work.dev_sample_2020 getnames=yes;
MPRINT(CSVEXL_IMPORT):   guessingrows=200000;
MPRINT(CSVEXL_IMPORT):   run;
MPRINT(BUILD_TABLE):  ;

[PaigeMiller]

%let deployMode = "dev";

You almost never put macro variable values in quotes, its hard for me to think of a reason why you would need quotes around macro variable values. Even though you remove the quotes later, SAS sometimes gets all queasy about the whole thing where you have removed quotes, and you have to use %UNQUOTE to fix it. Or just not put the value in quotes to begin with. 

[leehsin]

'%let deployMode = "dev";' is actually not in my code. It's a global macro variable that has many usages in our system. Let's not focus on modifying this macro variable to find a solution for the error.

[Kurt_Bremser]

This is most probably the cause of your problems:

%let deployMode = "dev";
%let env = %qsysfunc(compress(&deployMode.,%str(%")));

Don't complicate your life unnecessarily. Do this instead:

%let deployMode = dev;
%let env = &deployMode.;

[leehsin]

I can't change &deployMode like this:
%let deployMode = dev;
because this macro variable already exists in our system, and is used for the entire infrastructure.

I did not complicate my life unnecessarily. On the contrary, this change will make the life of our group much complicated.

[PaigeMiller]

---

@leehsin wrote:
I can't change &deployMode like this:
%let deployMode = dev;
because this macro variable already exists in our system, and is used for the entire infrastructure.

I did not complicate my life unnecessarily. On the contrary, this change will make the life of our group much complicated.

---

Well, @leehsin , I'm very skeptical. It's hard to see why you have to use this particular macro variable &DEPLOYMODE, why can't you name it something else? I also don't see why you can't just set &ENV as you want, rather than going through &deploymode. And you can also use %LOCAL macro variables to get around this. There are many solutions.

Nevertheless, if you insist, try this

out= work%unquote(&outfile)

[leehsin]

"%let deployMode = "dev";" exists in a configuration program which defines the whole system environment for multitasks, and my code is just for one of the tasks. I need to use unquoted "dev" for pooled data file names, and their paths, so I use &env to get the unquoted value from &deployMode. Of cause, I can assign &env directly using "%let env = dev;" and no issue occurred, but this is not my intention here as I wish &env could catch the value automatically from the settings existed in the environment.

[Kurt_Bremser · Accepted solution]

As you can see, the use of the unnecessary quotes already complicates your life, or you wouldn't be here. Letting it permeate is a loan you take out on your own future (and that of anybody else who needs to work in that environment). Correct it now.

Sometimes we need to bite the bullet and make corrections throughout a whole environment when such a mistake is unvovered, in order to guarantee a less complicated future. Been there, done that.

PS how many hundred programs would you need to change?

[leehsin]

Okay. I would bypass this approach if there was no solution for it. Thank you so much, KurtBremser !