DATA Step, Macro, Functions and more

Macro error referencing character variable

Reply
Occasional Contributor
Posts: 17

Macro error referencing character variable

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;

   %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!!!!

PROC Star
Posts: 7,364

Re: Macro error referencing character variable

: 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;

   %end;

      ;

run;

  

%Mend Keep;

  %Keep;

Occasional Contributor
Posts: 16

Re: Macro error referencing character variable

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;

   %end;

      ;

run;

   

%Mend Keep;

  %Keep;

Occasional Contributor
Posts: 16

Re: Macro error referencing character variable

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;

PROC Star
Posts: 7,364

Re: Macro error referencing character variable

: 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;

Ask a Question
Discussion stats
  • 4 replies
  • 280 views
  • 0 likes
  • 3 in conversation