0

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?

Improve this question
2
  • It sounds like you may want to use PROC RANK here with GROUPS=10? Commented Oct 24, 2018 at 17:53
  • I will read up about PROC RANK Commented Oct 25, 2018 at 8:29

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

nice, p.s. does the trimmed statement have any impact?
The timmed keyword will remove the spaces caused by converting a number to text so that it can be stored in a macro variable.
I've been looking for this my whole life
0

Are you trying to put 10 in a macro list, then you can use outobs= to limit the records. Question is which 10 records do you want when there are more than 10?

PROC SQL outobs=10;
SELECT X1
    INTO: BIN_X1 separated by ","
    FROM HAVE;
    QUIT;

    %put &Bin_x1;
Improve this answer

2 Comments

I'm looking to count the total number of distinct observations, if there are more than ten distinct obs i.e. above there is a range of 11 distinct entries then i'd force the variable to be 10
So implementing automatic binning? Use NLEVELS within PROC FREQ to get the number of distinct values and then use macro logic to determine if you need to bin and then how you want to bin. there are some written macros around if you're not interested in rolling your own: support.sas.com/resources/papers/proceedings17/0969-2017.pdf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.