topic Renaming all variables with a macro - ERROR 180-322 message in SAS Programming

topic Renaming all variables with a macro - ERROR 180-322 message in SAS Programming https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183807#M34909 <HTML><HEAD></HEAD><BODY><P>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?</P><P></P><P>/*1. Generate a sample dataset with long, random strings as variable names (use the code from <A href="http://support.sas.com/kb/51/386.html" title="http://support.sas.com/kb/51/386.html">51386 - Creating character variables that contain random alphabetic values</A>*/</P><P>data a(keep=final);</P><P>   length final $30;  </P><P>   do i=1 to 10;</P><P>      do j=1 to 30;</P><P>         substr(final,j)=byte(floor(65+26*ranuni(0)));</P><P>      end;</P><P>      output;</P><P>   end;</P><P>run;</P><P>proc sql noprint;</P><P>select final into :rand_names separetd by " " from a;</P><P>quit;</P><P></P><P>data sample;</P><P>input (&rand_names) (:best2.);</P><P>datalines;</P><P>1 2 3 4 5 6 7 8 9 10</P><P>;</P><P>run;</P><P></P><P><SPAN>/* 2. Macro that renames each variable name by selecting some characters from the original name. It's a modified version of the following macro: </SPAN><A class="jive-link-external-small" href="http://www2.sas.com/proceedings/sugi28/118-28.pdf*/">http://www2.sas.com/proceedings/sugi28/118-28.pdf*/</A></P><P></P><P>%macro rename; </P><P></P><P>proc sql noprint; </P><P>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*/</P><P></P><P>proc sql noprint; </P><P>select distinct(name) into :vars separated by " " from dictionary.columns  </P><P>where libname="WORK" and memname="SAMPLE" ; /* create macro variable with names separated by space*/</P><P>quit;</P><P></P><P></P><P>proc datasets library=WORK noprint; /* Rename variables, using datasets procedure*/</P><P>modify SAMPLE;</P><P>rename </P><P>    %do i=1 %to &num_vars;</P><P>    %let variable=%sysfunc(scan(&vars,&i)); /* a variable to rename*/</P><P>    %let new_var=%sysfunc(substr(&variable,3,2)); /*new, shorter name - 2 characters from the original name*/</P><P>       &variable=&new_var;</P><P>    %end;</P><P>;</P><P>quit;</P><P>run;</P><P></P><P>%mend rename;</P><P>%rename;</P><P></P><P>The LOG message is as follows</P><P></P><P>NOTE: PROCEDURE SQL used (Total process time):</P><P>      real time           0.00 seconds</P><P>      cpu time            0.00 seconds</P><P></P><P></P><P>NOTE: PROCEDURE SQL used (Total process time):</P><P>      real time           0.00 seconds</P><P>      cpu time            0.01 seconds</P><P></P><P></P><P>NOTE: Writing HTML Body file: sashtml.htm</P><P>NOTE: Renaming variable CVDHBIFOFXGLXNRHJMLGQVQNKUWAMZ to DH.         <------- it renames the first variable</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".                                    <--------- but fails to rename the others</P><P>1          DFYSDSYBLCZQTHNGJWCVMFHQCEPSKV</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          EXCUIOLRFKKSMVJYUVLKOHYANPLNYH</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          EYVOICPZSPBAVUOEGIUAANCUYFKNDL</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          FXBUOVXJZHHRMZLTFENRPEFJEDGCUN</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          JTKKGJWFIZIRARHMIEJEIMEKVPFSMR</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          NDTKWLEAPVYAWEFROLFGTKRIRELMZF</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          NFZXCHYDMGMGOLGFKWZVMNXPHXVARZ</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          QKZJOBBEKLLNCWSEYGAGYLHFCNVMHI</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          VLGARSNXSBLCIEVUZGQLLUFIHQBDHH</P><P>           ------------------------------</P><P>           180</P><P></P><P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P><P></P><P>NOTE: Statements not processed because of errors noted above.</P><P>NOTE: The SAS System stopped processing this step because of errors.</P><P>NOTE: PROCEDURE DATASETS used (Total process time):</P><P>      real time           0.81 seconds</P><P>      cpu time            0.26 seconds</P><P></P><P></P><P>The macro managed to rename the first variable, but failed to do this with the others. What am I doing wrong?</P><P></P><P>Thanks,</P><P></P><P>Chris</P></BODY></HTML> Wed, 03 Sep 2014 10:58:52 GMT chris2377 2014-09-03T10:58:52Z Renaming all variables with a macro - ERROR 180-322 message https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183807#M34909 <HTML><HEAD></HEAD><BODY><P>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?</P><P></P><P>/*1. Generate a sample dataset with long, random strings as variable names (use the code from <A href="http://support.sas.com/kb/51/386.html" title="http://support.sas.com/kb/51/386.html">51386 - Creating character variables that contain random alphabetic values</A>*/</P><P>data a(keep=final);</P><P>   length final $30;  </P><P>   do i=1 to 10;</P><P>      do j=1 to 30;</P><P>         substr(final,j)=byte(floor(65+26*ranuni(0)));</P><P>      end;</P><P>      output;</P><P>   end;</P><P>run;</P><P>proc sql noprint;</P><P>select final into :rand_names separetd by " " from a;</P><P>quit;</P><P></P><P>data sample;</P><P>input (&rand_names) (:best2.);</P><P>datalines;</P><P>1 2 3 4 5 6 7 8 9 10</P><P>;</P><P>run;</P><P></P><P><SPAN>/* 2. Macro that renames each variable name by selecting some characters from the original name. It's a modified version of the following macro: </SPAN><A class="jive-link-external-small" href="http://www2.sas.com/proceedings/sugi28/118-28.pdf*/">http://www2.sas.com/proceedings/sugi28/118-28.pdf*/</A></P><P></P><P>%macro rename; </P><P></P><P>proc sql noprint; </P><P>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*/</P><P></P><P>proc sql noprint; </P><P>select distinct(name) into :vars separated by " " from dictionary.columns  </P><P>where libname="WORK" and memname="SAMPLE" ; /* create macro variable with names separated by space*/</P><P>quit;</P><P></P><P></P><P>proc datasets library=WORK noprint; /* Rename variables, using datasets procedure*/</P><P>modify SAMPLE;</P><P>rename </P><P>    %do i=1 %to &num_vars;</P><P>    %let variable=%sysfunc(scan(&vars,&i)); /* a variable to rename*/</P><P>    %let new_var=%sysfunc(substr(&variable,3,2)); /*new, shorter name - 2 characters from the original name*/</P><P>       &variable=&new_var;</P><P>    %end;</P><P>;</P><P>quit;</P><P>run;</P><P></P><P>%mend rename;</P><P>%rename;</P><P></P><P>The LOG message is as follows</P><P></P><P>NOTE: PROCEDURE SQL used (Total process time):</P><P>      real time           0.00 seconds</P><P>      cpu time            0.00 seconds</P><P></P><P></P><P>NOTE: PROCEDURE SQL used (Total process time):</P><P>      real time           0.00 seconds</P><P>      cpu time            0.01 seconds</P><P></P><P></P><P>NOTE: Writing HTML Body file: sashtml.htm</P><P>NOTE: Renaming variable CVDHBIFOFXGLXNRHJMLGQVQNKUWAMZ to DH.         <------- it renames the first variable</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".                                    <--------- but fails to rename the others</P><P>1          DFYSDSYBLCZQTHNGJWCVMFHQCEPSKV</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          EXCUIOLRFKKSMVJYUVLKOHYANPLNYH</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          EYVOICPZSPBAVUOEGIUAANCUYFKNDL</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          FXBUOVXJZHHRMZLTFENRPEFJEDGCUN</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          JTKKGJWFIZIRARHMIEJEIMEKVPFSMR</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          NDTKWLEAPVYAWEFROLFGTKRIRELMZF</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          NFZXCHYDMGMGOLGFKWZVMNXPHXVARZ</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          QKZJOBBEKLLNCWSEYGAGYLHFCNVMHI</P><P>           ------------------------------</P><P>           180</P><P>NOTE: Enter RUN; to continue or QUIT; to end the procedure.</P><P>NOTE: Line generated by the macro variable "VARIABLE".</P><P>1          VLGARSNXSBLCIEVUZGQLLUFIHQBDHH</P><P>           ------------------------------</P><P>           180</P><P></P><P>ERROR 180-322: Statement is not valid or it is used out of proper order.</P><P></P><P>NOTE: Statements not processed because of errors noted above.</P><P>NOTE: The SAS System stopped processing this step because of errors.</P><P>NOTE: PROCEDURE DATASETS used (Total process time):</P><P>      real time           0.81 seconds</P><P>      cpu time            0.26 seconds</P><P></P><P></P><P>The macro managed to rename the first variable, but failed to do this with the others. What am I doing wrong?</P><P></P><P>Thanks,</P><P></P><P>Chris</P></BODY></HTML> Wed, 03 Sep 2014 10:58:52 GMT https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183807#M34909 chris2377 2014-09-03T10:58:52Z Re: Renaming all variables with a macro - ERROR 180-322 message https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183808#M34910 <HTML><HEAD></HEAD><BODY><P>do not put the semicolon in this line:          &variable=&new_var<SPAN style="color: #ff0000; font-size: 18pt;">;</SPAN></P><P>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</P><P>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.</P><P></P><P>Can be confusing as a semicolon has a distinct role        </P></BODY></HTML> Wed, 03 Sep 2014 11:14:27 GMT https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183808#M34910 jakarman 2014-09-03T11:14:27Z Re: Renaming all variables with a macro - ERROR 180-322 message https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183809#M34911 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P></P><P>You just have to delete the semicolon from <EM>&variable=&new_var;</EM></P><P></P><P>The rename syntax of proc datasets is:</P><P></P><P><EM>rename a=x b=y etc.</EM></P><P></P></BODY></HTML> Wed, 03 Sep 2014 11:15:36 GMT https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183809#M34911 Loko 2014-09-03T11:15:36Z Re: Renaming all variables with a macro - ERROR 180-322 message https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183810#M34912 <HTML><HEAD></HEAD><BODY><P>Thank you, Jaap and Loko. Now it works. Thanks for help</P></BODY></HTML> Wed, 03 Sep 2014 11:20:08 GMT https://communities.sas.com/t5/SAS-Programming/Renaming-all-variables-with-a-macro-ERROR-180-322-message/m-p/183810#M34912 chris2377 2014-09-03T11:20:08Z