DATA Step, Macro, Functions and more

Renaming all variables with a macro - ERROR 180-322 message

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Renaming all variables with a macro - ERROR 180-322 message

Dear all, I have a dataset with variables that have very long names. I am trying to write a macro that would rename all the variables by selecting some characters from the original name, but I get an "ERROR 180-322: Statement is not valid or it is used out of proper order" message. Could you please look at the code below and tell me what is wrong?

/*1. Generate a sample dataset with long, random strings as variable names (use the code from 51386 - Creating character variables that contain random alphabetic values*/

data a(keep=final);

   length final $30; 

   do i=1 to 10;

      do j=1 to 30;

         substr(final,j)=byte(floor(65+26*ranuni(0)));

      end;

      output;

   end;

run;

proc sql noprint;

select final into :rand_names separetd by " " from a;

quit;

data sample;

input (&rand_names) (:best2.);

datalines;

1 2 3 4 5 6 7 8 9 10

;

run;

/* 2. Macro that renames each variable name by selecting some characters from the original name. It's a modified version of the following macro: http://www2.sas.com/proceedings/sugi28/118-28.pdf*/

%macro rename;

proc sql noprint;

select nvar into :num_vars from dictionary.tables where libname="WORK" and memname="SAMPLE"; /*number of variables to rename, last value of the do loop below*/

proc sql noprint;

select distinct(name) into :vars separated by " " from dictionary.columns 

where libname="WORK" and memname="SAMPLE" ; /* create macro variable with names separated by space*/

quit;

proc datasets library=WORK noprint; /* Rename variables, using datasets procedure*/

modify SAMPLE;

rename

    %do i=1 %to &num_vars;

    %let variable=%sysfunc(scan(&vars,&i)); /* a variable to rename*/

    %let new_var=%sysfunc(substr(&variable,3,2)); /*new, shorter name - 2 characters from the original name*/

       &variable=&new_var;

    %end;

;

quit;

run;

%mend rename;

%rename;

The LOG message is as follows

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

NOTE: Writing HTML Body file: sashtml.htm

NOTE: Renaming variable CVDHBIFOFXGLXNRHJMLGQVQNKUWAMZ to DH.         <------- it renames the first variable

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".                                    <--------- but fails to rename the others

1          DFYSDSYBLCZQTHNGJWCVMFHQCEPSKV

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          EXCUIOLRFKKSMVJYUVLKOHYANPLNYH

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          EYVOICPZSPBAVUOEGIUAANCUYFKNDL

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          FXBUOVXJZHHRMZLTFENRPEFJEDGCUN

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          JTKKGJWFIZIRARHMIEJEIMEKVPFSMR

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          NDTKWLEAPVYAWEFROLFGTKRIRELMZF

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          NFZXCHYDMGMGOLGFKWZVMNXPHXVARZ

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          QKZJOBBEKLLNCWSEYGAGYLHFCNVMHI

           ------------------------------

           180

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

NOTE: Line generated by the macro variable "VARIABLE".

1          VLGARSNXSBLCIEVUZGQLLUFIHQBDHH

           ------------------------------

           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Statements not processed because of errors noted above.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.81 seconds

      cpu time            0.26 seconds

The macro managed to rename the first variable, but failed to do this with the others. What am I doing wrong?

Thanks,

Chris


Accepted Solutions
Solution
‎09-03-2014 07:14 AM
Trusted Advisor
Posts: 3,215

Re: Renaming all variables with a macro - ERROR 180-322 message

Posted in reply to chris2377

do not put the semicolon in this line:          &variable=&new_var;

Remember SAS macros are doing text string manipulation on SAS code. The ; will end the rename statement while your intention is to do al lot of them

Add a line with just a single semicolon after the macro do-loop. In that way you are getting the text/string of the SAS code correct.

Can be confusing as a semicolon has a distinct role       

---->-- ja karman --<-----

View solution in original post


All Replies
Solution
‎09-03-2014 07:14 AM
Trusted Advisor
Posts: 3,215

Re: Renaming all variables with a macro - ERROR 180-322 message

Posted in reply to chris2377

do not put the semicolon in this line:          &variable=&new_var;

Remember SAS macros are doing text string manipulation on SAS code. The ; will end the rename statement while your intention is to do al lot of them

Add a line with just a single semicolon after the macro do-loop. In that way you are getting the text/string of the SAS code correct.

Can be confusing as a semicolon has a distinct role       

---->-- ja karman --<-----
Super Contributor
Posts: 308

Re: Renaming all variables with a macro - ERROR 180-322 message

Posted in reply to chris2377

Hello,

You just have to delete the semicolon from &variable=&new_var;

The rename syntax of proc datasets is:

rename a=x b=y etc.

Contributor
Posts: 60

Re: Renaming all variables with a macro - ERROR 180-322 message

Thank you, Jaap and Loko. Now it works. Thanks for help

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 531 views
  • 3 likes
  • 3 in conversation