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.

Answer 2

Just use CALL EXECUTE() to generate the macro call.

data _null_;
  set cars_perct;
  call execute(cats('%nrstr(%percentiles_m)(',catx(',',of p_0--p_100),')'));
run;

Answer 3

A different approach - instead of macros use PROC FORMAT and the CNTLIN data set instead. Not sure this gets you exactly what you had before but close. You could modify it to get there for sure though. Just throwing it out as an option for formats that's a bit cleaner IMO.

proc transpsoe data=cars_perct out=cars_pct_long;
run;

data percentile_fmt;
    set cars_pct_long;
    fmtname='percentile_fmt';
    type='N';
    prev_value=lag(col1);
    start=prev_value;
end=col1;
label=catt(put(start, comma12.2), ' < ', put(end, comma12.2));

if _n_=1 then
    start=0;
run;

proc format cntlin=percentile_fmt;
run;

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