BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ryanb2
Quartz | Level 8
%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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

5 REPLIES 5
Astounding
PROC Star

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;

Ryanb2
Quartz | Level 8

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.

data_null__
Jade | Level 19

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;
ballardw
Super User

@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.

Shmuel
Garnet | Level 18

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 937 views
  • 3 likes
  • 5 in conversation