I am trying to create a PROC FORMAT Macro using Percentiles as the grouping limits

Question

I am trying to create a macro which will group a numerical variable in a data set by percentiles. I believe I have made some progress but not sure how I can pass the results from a PROC UNIVARIATE as macro variables into my PROC FORMAT Macro.

PROC UNIVARIATE DATA = SASHELP.CARS noprint;
    VAR INVOICE;
    
    OUTPUT OUT = work.cars_perct
        pctlpts= 0, 1, 5, 10, 25, 50, 75, 90, 95, 99, 100
        pctlpre= P_;
RUN;

PROC PRINT DATA = work.cars_perct;
RUN;

%MACRO Percentiles_m (P_0, P_1, P_5, P_10, P_25, P_50, P_75, P_90, P_95, P_99, P_100);
    PROC FORMAT;
        VALUE Percentile_fmt 
                        &P_0. -< &P_1. = '< ' &P_1.
                        &P_1. -< &P_5. = &P_1. ' < ' &P_5.
                        &P_5 -< &P_10 = &P_5. ' < ' &P_10.
                        &P_10 -< &P_25 = &P_10. ' < ' &P_25.
                        &P_25 -< &P_50 = &P_25. ' < ' &P_50.
                        &P_50 -< &P_75 = &P_50.' < ' &P_75.
                        &P_75 -< &P_90 =  &P_75. ' < ' &P_90.
                        &P_90 -< &P_95 =  &P_90. ' < ' &P_95.
                        &P_95 -< &P_99 = &P_95. ' < ' &P_99.
                        &P_99 -< &P_100 = &P_99. ' < ' &P_100.
                        ;
%MEND Percentiles_m;

%Percentiles_m(9875, 10642, 12830, 14375, 18851, 25294.5, 35732.5, 48377, 66830, 88324, 173560);

DATA work.Cars_2;
    SET sashelp.cars;
    invoice_2 = invoice;
    FORMAT invoice_2 Percentile_fmt.;
RUN;

PROC PRINT DATA = work.Cars_2;
RUN;

Any help would be appreciated.

Thank you

Answer 1

Transpose your output dataset so it is in a long format. You can then use proc sql to read them all into a single comma-separated macro variable and pass that into your macro function.

proc transpose data=cars_perct out=cars_perct_transpose;
    var _NUMERIC_;
run;

proc sql noprint;
    select COL1
    into :percentiles separated by ','
    from cars_perct_transpose
    ;
quit;

%Percentiles_m(&percentiles.);

If you view percentiles you'll see it is a comma-separated list:

%put &percentiles;

9875,10642,12830,14375,18851,25294.5,35732.5,48377,66830,88324,173560

If you want to save them all to individual macro variables, you can use call symputx and an array loop on cars_perct.

data _null_;
    set cars_perct;
    array pct[*] _NUMERIC_;

    do i = 1 to dim(pct);
        call symputx(vname(pct[i]), pct[i]);
    end;
run;

You now have macro variables &p_0, &p_1, &p_5, etc.