create a macro variable based on a condition SAS

Question

I have the following table

data have;
    input x1;
    datalines;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    ;
run;

and I'm trying to create a macro variable which allocates the number of distinct entries up to a maximum of ten so I can automatically create the right number of bins for a histogram later (max 10 bins)

the code i'm currently trying to leverage is as follows but I'm unsure how to allocate the macro variable when there are more than 10 distinct observations

PROC SQL NOPRINT;
    SELECT COUNT(DISTINCT X1)
        INTO: BIN_X1
        FROM HAVE
        HAVING (COUNT(DISTINCT X1) LE 10);
QUIT;

How do I allocate the macro variable to be 10 if there are more than 10 distinct obs?

Answer 1

Just use the SAS function min(,) to truncate the count to a maximum of 10.

proc sql noprint;
  select min(count(distinct x1),10)
    into :bin_x1 trimmed
  from have
  ;
quit;

So the SQL aggregate function count() will see how many distinct values of X1 exist in the dataset and then the SAS function min(,) will select the min of that number and 10.

Note that the SAS functions (min,max,etc) take two or more arguments and work just on those arguments for the current observation, but the SQL aggregate functions (min,max,etc) take only one argument and aggregate the values that argument represents across multiple observations.