topic Re: What's wrong with this SAS macro code? in New SAS User

topic Re: What's wrong with this SAS macro code? in New SAS User https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508084#M1598 <P>Make it a habit to always end macro variable references with a dot, and use double quotes for strings where macro variables need to be resolved:</P> <PRE>"C:\Users\...\&reg._&typ._&i..xlsx"</PRE> <P>Without the dot, the macro preprocessor tries to find macro variable reg_ instead of just reg. Note the double dot: one terminates the reference to &i, the other one stays in the string and goes into the filename.</P> <P>Do the same throughout your macro wherever you reference the macro variables.</P> Sun, 28 Oct 2018 07:32:34 GMT Kurt_Bremser 2018-10-28T07:32:34Z What's wrong with this SAS macro code? https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508083#M1597 <P>I have 2 files EU_CB_1 and EU_CB_2 in xlsx format. I want to import, append and format these 2 files using macro. I fail to understand why I get errors. Would you please help me identify the error?</P><P> </P><PRE>%macro impcol (reg,typ,start,count); %do i=&start %to &count; proc import datafile='C:\Users\...\&reg_&typ_&i.xlsx' dbms=xlsx replace out=&reg_&typ_&i; getnames=yes; run; data &reg_&typ; set &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' ' then delete; rename Country_Region=Region; rename Surv_M_=Surv_M; rename Surv_A_=Surv_A; rename Std_Dev=SD; rename Last_Rev_=Last_Rev; run; %end; %mend impcol; %impcol (EU,CB,1,2);</PRE><P>I get the following errors:</P><P> </P><P>2137 %macro impcol (reg,typ,start,count);<BR />2138 %do i=&start %to &count;<BR />2139 proc import datafile='C:\Users\Deepan\OneDrive\Brock Studies\Fall 2018\Thesis\Data\WIP\Eco<BR />2139! Data\Common\&reg_&typ_&i.xlsx'<BR />2140 dbms=xlsx replace out=&reg_&typ_&i;<BR />2141 getnames=yes;<BR />2142 run;<BR />2143<BR />2144 data &reg_&typ;<BR />2145 set &reg_&typ_&i;<BR />2146 drop Date_Time;<BR />2147 if Date=' ' and Event=' ' then delete;<BR />2148 rename Country_Region=Region;<BR />2149 rename Surv_M_=Surv_M;<BR />2150 rename Surv_A_=Surv_A;<BR />2151 rename Std_Dev=SD;<BR />2152 rename Last_Rev_=Last_Rev;<BR />2153 run;<BR />2154 %end;<BR />2155 %mend impcol;<BR />2156 %impcol (EU,CB,1,2);<BR />NOTE: Line generated by the invoked macro "IMPCOL".<BR />1 proc import datafile='C:\Users\Deepan\OneDrive\Brock Studies\Fall 2018\Thesis\Data\WIP\Eco<BR />1 ! Data\Common\&reg_&typ_&i.xlsx' dbms=xlsx replace out=&reg_&typ_&i; getnames=yes; run; data<BR />-<BR />22<BR />WARNING: Apparent symbolic reference REG_ not resolved.<BR />WARNING: Apparent symbolic reference TYP_ not resolved.<BR />NOTE: PROCEDURE IMPORT used (Total process time):<BR />real time 0.00 seconds<BR />cpu time 0.00 seconds</P><P>NOTE: The SAS System stopped processing this step because of errors.<BR />WARNING: Apparent symbolic reference REG_ not resolved.<BR />NOTE: Line generated by the invoked macro "IMPCOL".<BR />1 &reg_&typ_&i; getnames=yes; run;</P><P>-</P><P>200</P><P>ERROR 22-322: Expecting a name.</P><P>ERROR 200-322: The symbol is not recognized and will be ignored.</P><P><BR />NOTE: Line generated by the invoked macro "IMPCOL".<BR />1 data &reg_&typ; set</P><P>-</P><P>22</P><P>200<BR />ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;,<BR />_DATA_, _LAST_, _NULL_.</P><P>ERROR 200-322: The symbol is not recognized and will be ignored.</P><P>1 ! data &reg_&typ; set<BR />1 ! &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '<BR />-<BR />22<BR />WARNING: Apparent symbolic reference REG_ not resolved.<BR />WARNING: Apparent symbolic reference TYP_ not resolved.<BR />ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS,<BR />END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.</P><P>NOTE: Line generated by the invoked macro "IMPCOL".<BR />1 &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '</P><P>-<BR />-</P><P>200<BR />22</P><P>200<BR />ERROR: File WORK.REG_.DATA does not exist.<BR />ERROR: File WORK.TYP_1.DATA does not exist.</P><P>ERROR 200-322: The symbol is not recognized and will be ignored.</P><P>ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;,<BR />CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_,<BR />_NULL_.</P><P>WARNING: The variable Date_Time in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Country_Region in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Surv_M_ in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Surv_A_ in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Std_Dev in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Last_Rev_ in the DROP, KEEP, or RENAME list has never been referenced.<BR />NOTE: The SAS System stopped processing this step because of errors.<BR />WARNING: The data set WORK.REG_CB may be incomplete. When this step was stopped there were 0<BR />observations and 2 variables.<BR />WARNING: Data set WORK.REG_CB was not replaced because this step was stopped.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.05 seconds<BR />cpu time 0.04 seconds</P><P><BR />NOTE: Line generated by the invoked macro "IMPCOL".<BR />4 proc import datafile='C:\Users\Deepan\OneDrive\Brock Studies\Fall 2018\Thesis\Data\WIP\Eco<BR />4 ! Data\Common\&reg_&typ_&i.xlsx' dbms=xlsx replace out=&reg_&typ_&i; getnames=yes; run; data<BR />-<BR />22<BR />WARNING: Apparent symbolic reference REG_ not resolved.<BR />WARNING: Apparent symbolic reference TYP_ not resolved.<BR />NOTE: PROCEDURE IMPORT used (Total process time):<BR />real time 0.01 seconds<BR />cpu time 0.01 seconds</P><P>NOTE: The SAS System stopped processing this step because of errors.<BR />WARNING: Apparent symbolic reference REG_ not resolved.<BR />NOTE: Line generated by the invoked macro "IMPCOL".<BR />4 &reg_&typ_&i; getnames=yes; run;</P><P>-</P><P>200</P><P>ERROR 22-322: Expecting a name.</P><P>ERROR 200-322: The symbol is not recognized and will be ignored.</P><P><BR />NOTE: Line generated by the invoked macro "IMPCOL".<BR />4 data &reg_&typ; set</P><P>-</P><P>22</P><P>200<BR />ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;,<BR />_DATA_, _LAST_, _NULL_.</P><P>ERROR 200-322: The symbol is not recognized and will be ignored.</P><P>4 ! data &reg_&typ; set<BR />4 ! &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '<BR />-<BR />22<BR />WARNING: Apparent symbolic reference REG_ not resolved.<BR />WARNING: Apparent symbolic reference TYP_ not resolved.<BR />ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS,<BR />END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.</P><P>NOTE: Line generated by the invoked macro "IMPCOL".<BR />4 &reg_&typ_&i; drop Date_Time; if Date=' ' and Event=' '</P><P>-<BR />-</P><P>200<BR />22</P><P>200<BR />ERROR: File WORK.REG_.DATA does not exist.<BR />ERROR: File WORK.TYP_2.DATA does not exist.</P><P>ERROR 200-322: The symbol is not recognized and will be ignored.</P><P>ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;,<BR />CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_,<BR />_NULL_.</P><P>WARNING: The variable Date_Time in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Country_Region in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Surv_M_ in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Surv_A_ in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Std_Dev in the DROP, KEEP, or RENAME list has never been referenced.<BR />WARNING: The variable Last_Rev_ in the DROP, KEEP, or RENAME list has never been referenced.<BR />NOTE: The SAS System stopped processing this step because of errors.<BR />WARNING: The data set WORK.REG_CB may be incomplete. When this step was stopped there were 0<BR />observations and 2 variables.<BR />WARNING: Data set WORK.REG_CB was not replaced because this step was stopped.<BR />NOTE: DATA statement used (Total process time):<BR />real time 0.04 seconds<BR />cpu time 0.03 seconds</P><P> </P> Sun, 28 Oct 2018 07:10:25 GMT https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508083#M1597 d6k5d3 2018-10-28T07:10:25Z Re: What's wrong with this SAS macro code? https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508084#M1598 <P>Make it a habit to always end macro variable references with a dot, and use double quotes for strings where macro variables need to be resolved:</P> <PRE>"C:\Users\...\&reg._&typ._&i..xlsx"</PRE> <P>Without the dot, the macro preprocessor tries to find macro variable reg_ instead of just reg. Note the double dot: one terminates the reference to &i, the other one stays in the string and goes into the filename.</P> <P>Do the same throughout your macro wherever you reference the macro variables.</P> Sun, 28 Oct 2018 07:32:34 GMT https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508084#M1598 Kurt_Bremser 2018-10-28T07:32:34Z Re: What's wrong with this SAS macro code? https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508086#M1599 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/11562">@Kurt_Bremser</a>, thank you! I will never forget the pieces of advice.</P><P> </P><P>Regards.</P> Sun, 28 Oct 2018 07:42:37 GMT https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508086#M1599 d6k5d3 2018-10-28T07:42:37Z Re: What's wrong with this SAS macro code? https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508259#M1638 <P>You can find this advice as Maxim #48 in <A href="https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068" target="_blank">https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068</A></P> Mon, 29 Oct 2018 07:58:01 GMT https://communities.sas.com/t5/New-SAS-User/What-s-wrong-with-this-SAS-macro-code/m-p/508259#M1638 Kurt_Bremser 2018-10-29T07:58:01Z