topic Re: N, PCT,SUM, PCT SUM in SAS Programming

N, PCT,SUM, PCT SUM

Ronein — Sat, 04 Mar 2023 17:26:27 GMT

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

Re: N, PCT,SUM, PCT SUM

Cynthia_sas — Sun, 05 Mar 2023 01:57:18 GMT

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

Re: N, PCT,SUM, PCT SUM

ballardw — Tue, 07 Mar 2023 18:33:26 GMT

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;

Re: N, PCT,SUM, PCT SUM

Ronein — Tue, 07 Mar 2023 07:56:53 GMT

Can you show your proc report code for this task?

Re: N, PCT,SUM, PCT SUM

Cynthia_sas — Tue, 07 Mar 2023 18:31:06 GMT

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