topic Re: Problems with proc summary, total, other and order :) in SAS Procedures

Problems with proc summary, total, other and order :)

DanieleTiles — Wed, 13 Jan 2010 14:01:21 GMT

Hi to all,
we've got a problem on how to render a set of tabulates...let me explain: we've got to plot a table which shows, for city and year, the percentage of family with one person over the total of the families. We've than applied the proc summary, and got in this way the correct percentage :).
However, we need to apply a format to the cities, which groups the city for "association": we have this format which says for each city the correct association and:
- with start/end value of 999999 we point to the "All" association (in order to group them and show them in this kind of table...never let tabulate sum a percentage)
- with an entry of "No association" for the cities which don't apply to any format (it's a format with the Other option: no start or end).
This seems to work pretty well, except for the order on which the class variables are printed :(...unfortunately, the "No association" label is ALWAYS after the "All" label...and we are struggling to find a way to order in the other way! But even saying that order has to be "data" in the proc tabulate, and seeing that last observations are the total one...it doesn't work :(.
Any idea?
Thanks

Daniele

Re: Problems with proc summary, total, other and order :)

sbb — Wed, 13 Jan 2010 14:57:37 GMT

Share your code by pasting it into another REPLY with this post.

If you intend to chart/report values differently than in alphabetic-order, you will need to construct a FORMAT (numeric most likely) and assign representative values to achieve your desired order/sequence.

Scott Barry
SBBWorks, Inc.

Google advanced search argument suggested:

chart data values using format site:sas.com

Re: Problems with proc summary, total, other and order :)

Cynthia_sas — Wed, 13 Jan 2010 17:31:15 GMT

Hi:
I'm not entirely sure I understand what you want, but this note describes how to use PRELOADFMT and ORDER=DATA to ensure that you get the order you want with multi-label formats.
http://support.sas.com/kb/12/904.html

The simplified code below shows how to use TABULATE to get the formats in the order you want, with regular formats and with multilabel formats. The PRELOADFMT option loads the format as it is stored and the NOTSORTED in PROC FORMAT ensures that the format is stored in the order you defined. Either with or without multi-label formats, you can get the CLASS variables in the order you want.

cynthia
[pre]
proc format;
value agemlf (multilabel notsorted)
11-12='pre-teen'
13-19='teenager'
11-14='non-driver'
15-high='driver';

value regage (notsorted)
11-12 = 'Z'
13-14 = 'A'
15-high='H';
run;

proc sort data=sashelp.class out=class;
by age;
run;

proc tabulate data=class;
title 'PRELOADFMT and Formats In My Order';
class age / preloadfmt order=data;
var height;
table height*(min mean max),
age;
format age regage.;
run;

proc tabulate data=class;
title 'Using MLF and PRELOADFMT and Formats in My Order';
class age / mlf preloadfmt order=data;
var height;
table height*(min mean max),
age;
format age agemlf.;
run;
[/pre]

Re: Problems with proc summary, total, other and order :)

DanieleTiles — Wed, 13 Jan 2010 18:00:33 GMT

Hi to all,
thank you for your answer! I've found to be part of the problem the use of the preloadfmt, and I've solved 90% of the problems found.
Unfortunately, I cannot post the code...it's too much complicated and confused by macro code, and it would be just an headache for each one who would try to help me :).

Cynthia, your code looks quite interesting, but I've to ask something about the "notsorted" option: actually, we've got a program running each night which updates the formats.
However, we update the format using the cntlin option: we have prepared a table (downloaded from a SQL Server) which contains the minimum fields for creating a format...which field should I had for the "notsorted"?
Thanks

Daniele

Re: Problems with proc summary, total, other and order :)

data_null__ — Wed, 13 Jan 2010 18:52:31 GMT

An easy way to figure out what variables and values are needed for a CNTLIN data set is to look at a CNTLOUT data for similar formats/informats. I believe the answer to your question is to include S in the HLO variable.

[pre]

F
M
T S L
N T A
O A A E B H
b M R N E L
s E T D L O

1 AGEMLF 11 12 pre-teen SM
2 AGEMLF 13 19 teenager SM
3 AGEMLF 11 14 non-driver SM
4 AGEMLF 15 HIGH driver SMH
5 REGAGE 11 12 Z S
6 REGAGE 13 14 A S
7 REGAGE 15 HIGH H SH

[/pre]

Re: Problems with proc summary, total, other and order :)

DanieleTiles — Thu, 14 Jan 2010 20:41:20 GMT

Hi to all,
in the end we solved our problem by changing our format and avoiding using the "other" option in the formats...but still, I'd like to understand what I'm missing here.
Consider this sample code:

//////////////////////////////////////////////////////////////////////////

proc format cntlout=work.fmt;
value $first (notsorted) '1' = 'First'
other = 'None'
'999' = 'Total';

value $second (notsorted) '2' = 'Second'
other = 'None'
'999' = 'Total';
run;

data test;
length first $8.;
length second $8.;
length num 8.;
input first second num @@;
cards;
4 4 20
5 6 10
1 2 10
1 2 10
3 3 40
999 999 90
;

proc tabulate data=test;
class first / preloadfmt order=data;
class second / preloadfmt order=data;
var num;
table first='', second=''*num=''*sum='';
format first $first.;
format second $second.;
run;

//////////////////////////////////////////////////////////////////////////

What I expect (or would like) is a cross-table like thi one:

_______| Second | None | Total
First | | |
None | | |
Total | | |

Instead, what I get is this:

_______| Second | Total | None
First | | |
Total | | |
None | | |

What I'm doing wrong, missing?
Thanks

Daniele

Re: Problems with proc summary, total, other and order :)

Cynthia_sas — Thu, 14 Jan 2010 21:30:31 GMT

Hi:
As I remember, OTHER is a special category. It is always last. You might have gotten around it by:
[pre]
proc format;
value $first (notsorted) '1' = 'First'
2-998 = 'None'
'999' = 'Total';

value $second (notsorted) '2' = 'Second'
1, 3-998 = 'None'
'999' = 'Total';
run;
[/pre]

I don't believe you can use OTHER for this situation, given that your data are structured the way they are. For other reports, I have done something like this:
[pre]
value $first (notsorted) '1' = 'First'
2-998 = 'None'
other = 'Total';
[/pre]

But, this assumes that you know, from the structure of the data that only the 999 obs are left and that you will -never- have a number greater than 999 being used with this format.

cynthia

Re: Problems with proc summary, total, other and order :)

DanieleTiles — Thu, 14 Jan 2010 21:47:49 GMT

Hi Cynthia,
thank you very much for your answer! Finally, everything is explained :).
This was of course a simplified problem: the real problem was that the format is dynamically updated every night, and so we are sure that there aren't any cities with code '999999' (our total format), but...maybe tonight the city 199 is inside a format, tomorrow it's outside any category and then it should fall back to the "other" category
As we've got a complete list of the cities, we've done a simple code which associates each code of a city who doesn't fall into a format created by the user into a new "None" format, and we have created the format notsorted, ordered as we wanted. This is unfortunately less elegant than using the other format (which I found brilliant, until I was asked this particular order...), but it's still the best solution we could find.
Again, thanks for unveiling the problem 🙂