BookmarkSubscribeRSS Feed
letsdoit
Fluorite | Level 6

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

4 REPLIES 4
art297
Opal | Level 21

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

Jakkas
Calcite | Level 5

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;

Jakkas
Calcite | Level 5

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;

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1014 views
  • 0 likes
  • 3 in conversation