One way distribution of multiple variables: PCT from total without 0

[Ronein]

Hello,

I want to calculate distribution of multiple variables (One-way distribution for each variable).

In each distribution I want to calculate:

Count (number of rows in the category)

PCT( percent of number of rows in category from total rows)

PCT_without_0( Like PCT but without taking into account rows in category 0 if exists)

Here is my attempt to do it  .

The problems :

1- I don't know how to calculate PCT_without_0

2-The order of rows of distribution by Cylinders is not as I wish,I want the order will be same as in proc format:

'Null','3,4','5,6','8','10,12'

3-I want to put all distributions in one Report such that value in field column will not repeat

Here is wanted report,

What is the best way to do it?

proc format;
value Invoice_fmt
0='0'
0<-10000='0-10K'
10000<-20000='10K-20K'
20000<-30000='20K-30K'
30000<-40000='30K-40K'
40000<-high='40K+'
;
Run;
proc  format;
value Cylinders_Fmt
.='Null'
3,4='3,4'
5,6='5,6'
8='8'
10,12='10,12'
;
Run;


 
Data cars;
set SASHelp.cars;
IF _N_<=20 then  Invoice=0;
Run;
proc sql;
select put(Invoice,Invoice_fmt.) as Invoice,
       count(*) as count,
	   calculated count/(select count(*)  as total from cars )as pct format=percent8.1
from cars
group by calculated Invoice
;
quit;

proc sql;
select Origin,
       count(*) as count,
	   calculated count/(select count(*)  as total from cars )as pct format=percent8.1
from cars
group by Origin
order by count desc
;
quit;


proc sql;
select put(Cylinders,Cylinders_Fmt.) as Cylinders,
       count(*) as count,
	   calculated count/(select count(*)  as total from cars )as pct format=percent8.1
from cars
group by calculated Cylinders
;
quit;

[Ksharp]

proc format;
value Invoice_fmt
0='0'
0<-10000='0-10K'
10000<-20000='10K-20K'
20000<-30000='20K-30K'
30000<-40000='30K-40K'
40000<-high='40K+'
;
Run;
proc  format;
value Cylinders_Fmt
.='    Null'
3,4='   3,4'
5,6='  5,6'
8=' 8'
10,12='10,12'
;
Run;


 
Data cars;
set SASHelp.cars;
IF _N_<=20 then  Invoice=0;
Run;
proc sql;
create table temp1 as
select 'Invoice' as field length=80,put(Invoice,Invoice_fmt.) as Invoice length=80,
       count(*) as count,
	   calculated count/(select count(*)  as total from cars )as pct format=percent8.2,
case when calculated Invoice='0' then .
	 else  calculated count/(select count(*)  as total from cars where invoice ne 0)
end as pct_without0 format=percent8.2
from cars
group by calculated Invoice;

create table temp2 as
select 'Origin' as field length=80,Origin as Invoice length=80,
       count(*) as count,
	   calculated count/(select count(*)  as total from cars )as pct format=percent8.2,
	   calculated pct as  pct_without0 format=percent8.2
from cars
group by Origin
order by count desc;

create table temp3 as
select 'Cylinders' as field length=80,put(Cylinders,Cylinders_Fmt.) as Invoice length=80,
       count(*) as count,
	   calculated count/(select count(*)  as total from cars )as pct format=percent8.2,
	   calculated pct as  pct_without0 format=percent8.2
from cars
group by calculated Invoice
;
quit;
data want;
 set temp1-temp3;
run;
options missing=' ';
proc report data=want nowd spanrows;
define field/group order=data style(column)={vjust=m};
run;