topic Re: Creating a macro variable using a macro value in SAS Programming

Creating a macro variable using a macro value

Ryanb2 — Mon, 07 May 2018 21:59:18 GMT

%macro import_DD(In_DD,Out_DD);
PROC IMPORT OUT= WORK.&Out_DD._raw 
            DATAFILE= "C:\Projects\Data Dictionary_with_variable_names.xlsx" DBMS=EXCEL REPLACE; RANGE="'&In_DD.$'"; 
			GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;
%let import_DD_&Out_DD._err=&SYSERR;  
%put &import_DD_&Out_DD._err;
%mend import_DD;
%import_DD(DATA DICTIONARY,DD);
%import_DD(APPENDIX A1-CDPH State Codes,A1_DD);
%import_DD(APPENDIX A2- MARC (Country),A2_DD);
%import_DD(APPENDIX B-Country FIPS Codes,B_DD);
%import_DD(APPENDIX C_State FIPS Codes,C_DD);
%import_DD(APPENDIX D-Race Codes,D_DD);
%import_DD(APPENDIX G-County Codes,G_DD);
%import_DD(APPENDIX H-VS 10A,H_DD);
%import_DD(APPENDIX I-HOSPITAL CODES,I_DD);

The code above imports a data dictionary and appendices listed on different tabs in an Excel file. I'd like to assign &SYSERR after each import to a new macro variable so I can track whether or not there were errors during import, but I'm struggling to find code that will allow me to use the macro variable assigned at the top of the macro to name the new macro used to capture error codes. Any assistance is appreciated. Thanks!

Re: Creating a macro variable using a macro value

Astounding — Mon, 07 May 2018 22:50:18 GMT

The assignment with %LET is fine, although the macro variable will be created in the local symbol table. If you need it to be global so that it exists after the macro is over, add this statement before %LET:

%global import_DD_&Out_DD._err;

To get the %PUT statement to work, use a double-ampersand:

%put &&import_DD_&out_DD._err;

Re: Creating a macro variable using a macro value

data_null__ — Mon, 07 May 2018 22:50:57 GMT

Maybe you should make the variable(s) global so you have access to them after the macro runs.

%global import_DD_&Out_DD._err;
%let import_DD_&Out_DD._err = &syserr;

Re: Creating a macro variable using a macro value

ballardw — Mon, 07 May 2018 22:55:49 GMT

@Ryanb2 wrote:

The code above imports a data dictionary and appendices listed on different tabs in an Excel file. I'd like to assign &SYSERR after each import to a new macro variable so I can track whether or not there were errors during import, but I'm struggling to find code that will allow me to use the macro variable assigned at the top of the macro to name the new macro used to capture error codes. Any assistance is appreciated. Thanks!

Please describe exactly how you are struggling.

Currently the macro variable you are creating is only going to be local to that macro call. If you want to have it available after the macro terminates you will need a %global <your new macro variable here>; before assigning a value.

Re: Creating a macro variable using a macro value

Shmuel — Mon, 07 May 2018 23:28:46 GMT

Do you mean something like:

%global run_no;
%let run_no = 0;
%macro import_DD(In_DD,Out_DD);
   %let run_no = %eval(&run_no + 1);
   %let import_rc = r&run_no._err;
    PROC IMPORT OUT= WORK.&Out_DD._raw 
          DATAFILE= "C:\Projects\Data Dictionary_with_variable_names.xlsx" 
         DBMS=EXCEL REPLACE; RANGE="'&In_DD.$'"; 
	GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; 
        SCANTIME=YES; 
   RUN;
   %let &import_rc = &syserr;
   %put &import_rc is &&import_rc;
  /********
    %let import_DD_&Out_DD._err=&SYSERR;  
    %put &import_DD_&Out_DD._err;
 *********/
%mend import_DD;
%import_DD(DATA DICTIONARY,DD);
%import_DD(APPENDIX A1-CDPH State Codes,A1_DD);
... etc. ....

You will get in log:

r1_err is ..... /* 1st macro run syserr */

r2_err is .... /* 2nd macro run syserr */

Re: Creating a macro variable using a macro value

Ryanb2 — Tue, 08 May 2018 14:49:16 GMT

I apologize for the miscommunication. I set these macros to global in an earlier section of code, so that was not the issue. I was getting an error message from the %put statement "WARNING: Apparent symbolic reference IMPORT_CCBF_DD_ not resolved.
&import_CCBF_DD_CCBF_DD_err"

I believed this to be a problem with the creation of the macro, but the issue was in the %put statement, solved by adding the second & (%put &&import_DD_&out_DD._err;) mentioned by Astounding. In hindsight I should have set the macro variable to blank then run the code again. It was my belief the value assigned to the macro was from an earlier attempt.

Thanks for everyone's input. It was very helpful.