topic Re: proc format in SAS Programming

Building format from Univariate output

Ronein — Tue, 07 Dec 2021 07:06:16 GMT

Hello

I am using proc univariate to calculate percentiles 10,20,30,40,50,60,70,80,90,100

The outcome data set is called pctls and is getting the values

Data pctls;
input REVENUEpct10	REVENUEpct20	REVENUEpct30	REVENUEpct40	REVENUEpct50	REVENUEpct60	REVENUEpct70	REVENUEpct80	REVENUEpct90	REVENUEpct100;
cards;
0	0	0	4	94.4325	1642.59	7241.93	18418.335	44282.98	27846721.81
;
Run;

Then I want to build formats


proc transpose
  data=pctls
  out=p_long;
var _all_;
run;


data cntlin;
set p_long;
length label $100.;
fmtname = "RevenueFmt";
type = "N";
start = ifn(_n_ = 1,0,lag(col1));
end = col1;
label = catx(" ","group_Revenue",_n_,"start:",start,"end:",end);
keep fmtname type start end label;
run;


proc format cntlin=cntlin;
run;

The problem is values 0-0 are overlapped.

What do you suggest to do in order to solve this problem and build the format well?

Re: proc format

Kurt_Bremser — Sun, 05 Dec 2021 08:48:47 GMT

So this means that 30+ percent of your population have a zero value. I would discard the lower percentiles having a zero value:

data p_long2;
set p_long;
by col1,
if last.col1;
run;

Re: proc format

Reeza — Sun, 05 Dec 2021 19:15:30 GMT

PROC RANK with GROUPS=10 will put your data into deciles directly.

EG.

proc sort data=sashelp.cars out=cars;
by mpg_city;
run;

proc rank data=cars groups=10 out=want;
var mpg_city;
ranks rank_mpg;
run;

Now the variable rank_mpg has your data ranked into deciles from 0 to 9 for your ten groups.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/p16s2o8e4bnqrin1phywxdaxqba7.htm