topic Re: Summary Tables with a Column % with values like '5E3%' ? in SAS Enterprise Guide

Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Tue, 07 Oct 2014 23:57:51 GMT

Hi Forum,

I am creating a Summary table from Numeric data in SAS EG 5.1 64 bit.

All goes well until I add the Format for a Percentage of each Column as I get strange results like the above.

100% seems to appear as 1E4%.

I've tried all the % type Formats but no luck getting a normal number?

Can anyone advise what's going on?

Thanks.

Re: Summary Tables with a Column % with values like '5E3%' ?

Reeza — Wed, 08 Oct 2014 00:41:32 GMT

Are your numbers between 0 and 1?

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 00:45:10 GMT

Hi Reeza,

No, the values are 8 digit Numeric Codes like '23432344' & I'm counting them by 2 Areas.

So I have a summary of Area 1 which is a count of those Codes & the same for Area 2.

Re: Summary Tables with a Column % with values like '5E3%' ?

Patrick — Wed, 08 Oct 2014 00:57:17 GMT

Could it be that the internal value for 100% is actually 100 instead of 1?

data test;

format var1 var2 percent. var3 percent8.;

var1=100;

var2=1;

var3=100;

run;

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 00:58:33 GMT

That could well be the case Patrick, do you know how I can get it to display numbers though?

Re: Summary Tables with a Column % with values like '5E3%' ?

Patrick — Wed, 08 Oct 2014 01:00:03 GMT

You use a different format, eg. best32.

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 01:08:59 GMT

That change to BEST32 gives a Number but I want it to show the % in the Table Cell next to the Number.

The Column Description has % in it, so I'm wondering if getting the Table Cell to also show 46% for example is possible?

Re: Summary Tables with a Column % with values like '5E3%' ?

Patrick — Wed, 08 Oct 2014 01:22:13 GMT

If you want to see percent then you should use the correct internal values. 50% is the same like 0.5 - it's not a value of 50 with a percent sign.

I suggest that you amend your calculations accordingly.

It would be possible to add a '%' sign to any value using a picture format - but I would call this "deceptive".

proc format;

picture cheat

other='0000000%'

;

run;

data test;

format var1 cheat.;

var1=50;

run;

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 01:31:38 GMT

I would have thought SAS would be able to add a number of values in categories & derive a % of each category regardless of the format.

The values it is counting could be 0.5 but this would still be a % of 50 '0.5s' out of a population of 200 values & therefore give me 25%.

Re: Summary Tables with a Column % with values like '5E3%' ?

Cynthia_sas — Wed, 08 Oct 2014 01:36:03 GMT

Hi:

The SAS Percent format does an automatic multiply by 100 -- for the purpose of turning your decimal number (like .05 and .23 into the percents 5% and 23%). So if you have integer numbers in the cell that have already been multiplied by 100, then the Percent format is going to do another multiply by 100, which will not be what you want.

For example, when you are using PROC TABULATE, which also does a multiply by 100 for it's internally calculated percents, you CANNOT use the SAS Percent format because then you will multiply by 100 two times. This is where, with TABULATE, you MUST use a PICTURE format such as shown below to get the % into the cell.

cynthia

proc format;

picture mypct low-high='009.99%';

run;

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 01:41:42 GMT

Thanks Cynthia,

So is that why I'm getting a scientific number?

If that's what it is?

Re: Summary Tables with a Column % with values like '5E3%' ?

Patrick — Wed, 08 Oct 2014 02:04:49 GMT

As shown in the screenshot I've posted the default length of the percent format 'percent.' is too short to show 10000%. That's why SAS uses scientific notation. When using the format with a increased length 'percent8.' then you get 10000% (100*100).

Re: Summary Tables with a Column % with values like '5E3%' ?

Reeza — Wed, 08 Oct 2014 02:07:56 GMT

Oscarboots wrote:

I would have thought SAS would be able to add a number of values in categories & derive a % of each category regardless of the format.

The values it is counting could be 0.5 but this would still be a % of 50 '0.5s' out of a population of 200 values & therefore give me 25%.

It can, you're just not doing it right

Perhaps start your question from scratch and we can help you get where you want to go.

For example, the summary you're looking for sounds like something you'd do via Task>Describe>One way Frequencies or Table Analysis

Then look into all the options to make sure you add the percents the way you'd like.

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 02:29:04 GMT

Correct Reeza!

Here are my steps;

Select the data Node I need, go to Tasks >> Describe >> Summary tables Wizard
To the 'Analysis variables', I add the 'OrderActualID' field with the statistic '% of Column Frequency'
Next, I add 'RoundID' to the 'Columns' area
'Spend_Category' to the 'Rows' area
'Week_Ending' to the 'Pages' area
That's it to give me the actual %s of the % of Column for 'OrderActualID' but it doesn't display a '%' sign next to the number
It's when I add the Format of 'PERCENT6.1' in Step 2 of the process under 'Select table Format' is when I get the scientific format??

Any suggestions

Re: Summary Tables with a Column % with values like '5E3%' ?

Reeza — Wed, 08 Oct 2014 02:52:00 GMT

I stand corrected, it can't :smileyangry:

You'll have to create a custom format and apply as indicated by Cynthia

However, I still think you have a mistake in your process. The analysis is typically something you'll add, average, take the median and orderID doesn't sound like some thing like that.

Re: Summary Tables with a Column % with values like '5E3%' ?

Oscarboots — Wed, 08 Oct 2014 02:59:29 GMT

Ok, does anyone know how I can apply the 'Picture' solution using the Wizard?

I'd assume that I create the Summary table with the Wizard & then ad the Proc format into the script.

Any details on where I add it or if I'm on the right track?

Thanks

Re: Summary Tables with a Column % with values like '5E3%' ?

Reeza — Wed, 08 Oct 2014 03:21:43 GMT

I think you first need to create format under Tasks>Data>Create Format