Dynamic Macro Loop

ka2yee — Mon, 03 Apr 2017 17:46:29 GMT

Hello,

I can't seem to get the piece of code identified below to work. I'm trying to get the maximum # of distinct values for the age_group, urban, and urban_zone variables. Use these numbers to subsequently run a do loop to store each of the disinct values into macros to retrieve later. I'd like to do this dynamically. Hope this makes sense.

Appreciate any help.

/**************************************************************************************/
/*store values from macro loops for retrieve in later step to calculate weights & prevalence rates*/
/**************************************************************************************/
/*create global macros for the 3 variables of interest*/
%LET LBL1=age_group;
%LET LBL2=urban;
%LET LBL3=urban_zone;

/*obtain list of distinct categories for each of the 3 variables of interest*/
/*obtain the total number of distinct categories for each of the 3 variables of interest*/
/*obtain the maximum number of distinct categories for each of the 3 variables of interest*/
%macro var_for_val(dataset,var);
    proc sql noprint;
        create table dist_var_&&LBL&k as
        select distinct &var
        from &dataset;
    quit;

    proc sql noprint;
        create table dist_var2_&&LBL&k as
        select monotonic() as id, &var
        from dist_var_&&LBL&k;
    quit;

%global max_var_&&LBL&k;
proc sql noprint;
   select distinct max(id)
   into :max_var_&&LBL&k
  from dist_var2_&&LBL&k;
quit;

/*CANNOT GET THIS PIECE OF CODE TO WORK - seems not to recognize the value but instead the macro name*/

%do i = 1 %to &max_var_&&LBL&k;
        %global var_&&LBL&k_&i;
        proc sql noprint;
            select distinct &var
            into :var_&&LBL&k_&i
            from dist_var2_&&LBL&k
            where id = &i;
        quit;
        %put &&var_&&LBL&k_&i;
    %end;

/*THIS PIECE OF CODE NOT WORKING END*/

%mend var_for_val;

%macro geo_var;

%do k=1 %to 3;
%var_for_val (pop,var=&&LBL&k);
%end;
%mend geo_var;

/*run the geo_var macro to get the distinct values for each of the variables of interest*/
%geo_var;
/*display all the macro values*/
%put _ALL_;

Re: Dynamic Macro Loop

Tom — Mon, 03 Apr 2017 18:20:00 GMT

Do you just want the number of observations in the table the first step creates?

If so then just use SQLOBS automatic macro variable.

To make GLOBAL macro variables it is much easier to use CALL SYMPUTX().

%macro var_for_val(dataset,var);
proc sql noprint;
  create table dist_var_&var as
    select distinct &var
    from &dataset
  ;
%global max_var_&var;
%let max_var_&var=&sqlobs;
quit;

data dist_var_&var ;
  id+1;
  set dist_var_&var;
  call symputx(cats("dist_var_&var",id),&var,'G');
run;

%mend var_for_val;

Note you should probably re-engineer your process to not require so many macro variables. It is usually better to keep data in datasets rather than forcing it into text strings in macro variables.

Re: Dynamic Macro Loop

ka2yee — Mon, 03 Apr 2017 18:54:48 GMT

Thanks Tom. Your right its not very efficient what I'm thinking. I'll re-engineer the process. I appreciate your help. Best,

Karen

topic Dynamic Macro Loop in SAS Programming

Dynamic Macro Loop

Re: Dynamic Macro Loop

Re: Dynamic Macro Loop