topic Re: Macro error referencing character variable in SAS Programming

Macro error referencing character variable

letsdoit — Wed, 11 Sep 2013 16:49:45 GMT

Hi, I have the following code

%let STATE1=AL; %let STATE2=AK; %let STATE3=AZ; %let STATE4=AR; %let STATE5=CA; %let STATE6=CO; %let STATE7=CT;

%let STATE8=DE; %let STATE9=DC; %let STATE10=FL; %let STATE11=GA; %let STATE12=HI; %let STATE13=ID; %let STATE14=IL;

%let STATE15=IN; %let STATE16=IA; %let STATE17=KS; %let STATE18=KY; %let STATE19=LA; %let STATE20=ME; %let STATE21=MD;

%let STATE22=MA; %let STATE23=MI; %let STATE24=MN; %let STATE25=MS; %let STATE26=MO; %let STATE27=MT; %let STATE28=NE;

%let STATE29=NV; %let STATE30=NH; %let STATE31=NJ; %let STATE32=NM; %let STATE33=NY; %let STATE34=NC; %let STATE35=ND;

%let STATE36=OH; %let STATE37=OK; %let STATE38=OR; %let STATE39=PA; %let STATE40=RI; %let STATE41=SC; %let STATE42=SD;

%let STATE43=TN; %let STATE44=TX; %let STATE45=UT; %let STATE46=VT; %let STATE47=VA; %let STATE48=WA; %let STATE49=WV;

%let STATE50=WI; %let STATE51=WY;

%let YEAR1=2010; %let YEAR2=2011; %let YEAR3=2013;

%Macro Keep;

%do i = 1 %to 1;

%do j = 3 %to 3;

data by_state_&&i.;

set data_2012;

where state=&i;

run;

%end;

;

run;

%Mend Keep;

%Keep;

I have database with data for each state and I am trying to do calculations by state. However when I run the code above - I tried having the &I in "" and with and without the dot after it (&I and &I.), but I either get this error message "ERROR: WHERE clause operator requires compatible variables." or the data doesn't gets picked up because lets say for example the %do i = 1 %to 1; doesn't pick up that I am looking for state AL but refers it as to 1

iMPRINT(KEEP): data by_state_1;

iMPRINT(KEEP): set data_2012;

iMPRINT(KEEP): where state="1";

iMPRINT(KEEP): run;

and doesn't find any data . What am I doing wrong?

Thanks!!!!

Re: Macro error referencing character variable

art297 — Wed, 11 Sep 2013 18:08:57 GMT

: Not really sure what your data look like or what you are trying to do. Does the following come close?

%let STATE1=AL; %let STATE2=AK; %let STATE3=AZ; %let STATE4=AR; %let STATE5=CA; %let STATE6=CO; %let STATE7=CT;

%let STATE8=DE; %let STATE9=DC; %let STATE10=FL; %let STATE11=GA; %let STATE12=HI; %let STATE13=ID; %let STATE14=IL;

%let STATE15=IN; %let STATE16=IA; %let STATE17=KS; %let STATE18=KY; %let STATE19=LA; %let STATE20=ME; %let STATE21=MD;

%let STATE22=MA; %let STATE23=MI; %let STATE24=MN; %let STATE25=MS; %let STATE26=MO; %let STATE27=MT; %let STATE28=NE;

%let STATE29=NV; %let STATE30=NH; %let STATE31=NJ; %let STATE32=NM; %let STATE33=NY; %let STATE34=NC; %let STATE35=ND;

%let STATE36=OH; %let STATE37=OK; %let STATE38=OR; %let STATE39=PA; %let STATE40=RI; %let STATE41=SC; %let STATE42=SD;

%let STATE43=TN; %let STATE44=TX; %let STATE45=UT; %let STATE46=VT; %let STATE47=VA; %let STATE48=WA; %let STATE49=WV;

%let STATE50=WI; %let STATE51=WY;

%let YEAR1=2010; %let YEAR2=2011; %let YEAR3=2013;

options mprint mlogic symbolgen;

data data_2012;

input state $ x;

cards;

AL 1

AK 1

AZ 3

;

%Macro Keep;

%do i = 1 %to 1;

%do j = 3 %to 3;

data by_state_&i.;

set data_2012;

where state="&&state&i";

run;

%end;

;

run;

%Mend Keep;

%Keep;

Re: Macro error referencing character variable

Jakkas — Wed, 11 Sep 2013 18:10:10 GMT

Try the following

%Macro Keep;

%do i = 1 %to 1;

%do j = 3 %to 3;

data by_state_&i.;

set data_2012;

where state="&&STATE&i";

run;

%end;

;

run;

%Mend Keep;

%Keep;

Re: Macro error referencing character variable

Jakkas — Wed, 11 Sep 2013 18:20:50 GMT

Also a shorter and cleaner way to create those state macro variables would be

Data Temp;

input ST :$2. @@;

call Symputx("STate"||Left(Put(_n_,12.)),ST);

datalines;

AL AZ OK

run;

Re: Macro error referencing character variable

art297 — Wed, 11 Sep 2013 19:42:43 GMT

: Conversely, if and I correctly interpreted what you are trying to do, the following would definitely be a lot more generalizable (I think):

data temp (index= (state));

set data_2012;

run;

proc sql noprint;

select catt("'",name,"'")

into :vars separated by ","

from dictionary.columns

where libname="WORK" and

memname="DATA_2012"

;

quit;

data _null_;

dcl hash hh ( ) ;

hh.definekey ('k' ) ;

hh.definedata (&vars.);

hh.definedone ();

do k= 1 by 1 until ( last.state ) ;

set temp ;

by state ;

hh.add ();

end ;

hh.output (dataset: "by_state_"||state);

run;