N, PCT,SUM, PCT SUM

[Ronein]

Hello

I want to use sashelp.cars  data set to create the following report.

For each Origin category to calculate:

Number of observations (Nr)

Percent of observations from total observations (PCT)

Sum of MSRP (Sum_MSRP)

Percentage of sum MSRP  from total (PCT_MSRP)

I want to dissplay percentage in %  with format percent8.1

I want to define Europe+USA  as one category together

I want that order of rows will be from highest freq (desending)

I want to diaplay sum_MSRO  in millions 

What is the way to do it via-

proc means

proc tabulate

proc Report

proc sql

[Cynthia_sas]

Hi:
What code have you tried? My tendency would be to use either PROC REPORT or PROC TABULATE. I expect that you'll need a user-defined format to have Europe and USA treated as one category. Otherwise, the statistics are fairly straightforward with both PROC TABULATE and PROC REPORT. I'm probably learning more toward TABULATE at this point.
Cynthia

[Ronein]

Can you show your proc report code for this task?

[Cynthia_sas]

Hi:

  Sure, here's the PROC REPORT code. I used 2 of your formats, but did not use the fakepct format -- that's was only needed for TABULATE. REPORT can use the regular SAS PERCENT format. I used 2 decimal places to show how the percents do add up to 100.00%

Cynthia

[ballardw]

If your "what is the way" is expecting 4 solutions, why? Pick one and know why, or show what you have at least attempted for each.

Personally I wouldn't even consider SQL for this because you have to merge multiple elements after figuring out how to do the basic calculations.

Formats. You have three pieces that require custom formats, depending on approach.

The reason I say three is because in the two most likely solutions I would do, Proc Tabulate and Report the Pct is not an actual decimal value. So you have to fudge to get a percent sign. You also don't say anything about how many decimals you want in your "millions" result.

My take:

proc format;
value $myorigin
'Asia' = 'Asia'
'USA','Europe' = 'Europe, USA'
; 
picture fakepct
low-high = '009.9%' ;
picture millions (fuzz=0)
   low-high='0000 M' ( mult=.000001)
;
run;

proc tabulate data=sashelp.cars;
   class origin/order=freq;
   format origin $myorigin.;
   var msrp   ;              ;
   table origin=' ' All='Total',
         n='Nr' pctn='PCT'*f=fakepct. 
         msrp=' '*(sum='Sum_MSRP'*f=millions. pctsum='PCT_MSRP'*f=fakepct.)
         /box='Origin'
   ;
run;