BookmarkSubscribeRSS Feed
ka2yee
Calcite | Level 5

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.

 

K.

 

/**************************************************************************************/
/*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_;

2 REPLIES 2
Tom
Super User Tom
Super User

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.

ka2yee
Calcite | Level 5

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

 

Karen

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
  • 2 replies
  • 370 views
  • 0 likes
  • 2 in conversation