Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- proc report across usage

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-29-2022 04:48 PM
(256 views)

G'day,

I have the following program: Part 1 & 2 runs without SAS errors except Part 3 doesn't run. I'm trying to add pctud so that is shows under type (i.e, ask SAS to calculate pctutd across the two different types) and also ask SAS to calculate it for the combined types (which is equivalent to pctutd in Part 2) so it will have 3 columns with pctutd: the first column (after locale, year, grade, and children) with pctutd that sums utd for all types (type=class & nonclass) and divides it by the totalchildren for all types, the 2nd column that sums utd for observations with type=class and divides it by totalchildren for type=class and the 3rd column with pctutd for nonclass, all grouped by locale and year. Any help you can give will be greatly appreciated. And it will have a last column that has the % of entities that have nonclass type: so sas counts and sums the number of entities that have nonclass type for example in locale North and divides it by the total of entities in locale North, etc... Thank you.

*Part 1--dataset; data have; input locale $ year $ grade $ totalchildren utd type $; datalines; North 2020 5th 120 119 class South 2020 5th 140 120 nonclass East 2020 5th 18 10 nonclass West 2020 5th 9 9 class North 2020 5th 11 9 class ; *Part 2--program that has no errors except it doesn't give output I want; proc report data = have out=want nowd missing completerows ; column locale year grade totalchildren utd pctutd type; define locale / group 'Locale'; define year / group; define grade / group; define totalchildren / analysis sum; define utd / analysis noprint; define pctutd / computed; compute pctutd; pctutd=utd.sum/totalchildren.sum; endcompute; define type / across 'Type'; *--summarizes ALL; rbreak before /summarize; compute before; locale= 'ALL'; year="current"; grade="5th"; endcomp; run;

Current output

Type | ||||||
---|---|---|---|---|---|---|

Locale | year | grade | totalchildren | pctutd | class | nonclass |

ALL | current | 5th | 298 | 0.8959732 | 3 | 2 |

East | 2020 | 5th | 18 | 0.5555556 | 0 | 1 |

North | 2020 | 5th | 131 | 0.9770992 | 2 | 0 |

South | 2020 | 5th | 140 | 0.8571429 | 0 | 1 |

West | 2020 | 5th | 9 | 1 | 1 | 0 |

```
*--part 3 - nonworking program
proc report data = have out=want nowd missing completerows ;
column locale year grade totalchildren type,pctutd
```

('Overall' type pctutd);
define locale / group 'Locale';
define year / group; define grade / group;
define totalchildren / analysis sum;
define utd / analysis noprint;
define pctutd / computed;
compute pctutd;
pctutd=utd.sum/totalchildren.sum;
endcompute;
define type / across 'Type';
*--summarizes ALL;
rbreak before /summarize;
compute before;
locale= 'ALL';
year="current";
grade="5th";
endcomp;
run;

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi: Take a look at this paper: https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf starting on page 7. You need use absolute column numbers when you want to compute a value that's UNDER an ACROSS item.

Cynthia

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Cynthia,

Appreciate your guidance.

I see the compute block needs to be different from what I have for pctutd for the ACROSS usage.

I tried using the following (not sure which column SAS sees utd as since I don't have it listed in the column statement

compute pctutd;

pctutd=c5.sum/c4.sum;

endcompute;

and I'm now I"m getting the following error message:

ERROR: The variable type of C5.SUM is invalid in this context.

ERROR: The variable type of C4.SUM is invalid in this context.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi:

When I use the absolute column reference it is ALWAYS _C2_, _C3_, _C4_, etc...you need those underscores. And, the good news is that because it is the ABSOLUTE column number, you don't need the .sum as you would with a "regular" numeric item. So you should be able to simplify your formula to something like:

PCTUTD = _C5_ / _C4_;

Without data it's hard to guess. I thought I showed using OUT= in the paper to be able to understand which column numbers belonged to which column so you could get the numbers right. But, also remember, if PCTUTD is UNDER TYPE, then if you have 2 types, you'll also get absolute column numbers for two values for PCTUTD under each TYPE as well. Something like this -- these columns are totally made up, because I don't quite understand your COLUMN statement without data to run through the program to double check.

_C6_ = _C5_ / _C4_;

_C8_ = _C7_/_C4_;

I see that you're using UTD in Part 2 and I see UTD in a DEFINE in Part 3, but why isn't UTD on the COLUMN statement in Part 3? You'll probably get an error without UTD in the COLUMN statement and if you use PCTUTD under TYPE and not under TYPE, then you'll have a problem too with the COMPUTE block. Also, when you calculate PCTUTD under each value of TYPE, don't you also need the TOTALCHILDREN value for each TYPE so the division works out. I can't figure out from your posted data example, what the right numbers should be. Can you mock up something with the actual numbers that you expect to get based on the data you posted? Because otherwise, it's very hard to understand what is getting divided by what to produce PCTUTD.

Also, I'd recommend ditching the final COMPUTE block for ALL right now and concentrate on getting the overall column header structure and numbers right every place else and then do the COMPUTE block for ALL because you don't want to fiddle with it while you're getting everything else just right.

Cynthia

When I use the absolute column reference it is ALWAYS _C2_, _C3_, _C4_, etc...you need those underscores. And, the good news is that because it is the ABSOLUTE column number, you don't need the .sum as you would with a "regular" numeric item. So you should be able to simplify your formula to something like:

PCTUTD = _C5_ / _C4_;

Without data it's hard to guess. I thought I showed using OUT= in the paper to be able to understand which column numbers belonged to which column so you could get the numbers right. But, also remember, if PCTUTD is UNDER TYPE, then if you have 2 types, you'll also get absolute column numbers for two values for PCTUTD under each TYPE as well. Something like this -- these columns are totally made up, because I don't quite understand your COLUMN statement without data to run through the program to double check.

_C6_ = _C5_ / _C4_;

_C8_ = _C7_/_C4_;

I see that you're using UTD in Part 2 and I see UTD in a DEFINE in Part 3, but why isn't UTD on the COLUMN statement in Part 3? You'll probably get an error without UTD in the COLUMN statement and if you use PCTUTD under TYPE and not under TYPE, then you'll have a problem too with the COMPUTE block. Also, when you calculate PCTUTD under each value of TYPE, don't you also need the TOTALCHILDREN value for each TYPE so the division works out. I can't figure out from your posted data example, what the right numbers should be. Can you mock up something with the actual numbers that you expect to get based on the data you posted? Because otherwise, it's very hard to understand what is getting divided by what to produce PCTUTD.

Also, I'd recommend ditching the final COMPUTE block for ALL right now and concentrate on getting the overall column header structure and numbers right every place else and then do the COMPUTE block for ALL because you don't want to fiddle with it while you're getting everything else just right.

Cynthia

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.