Help using Base SAS procedures

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

Reply
Contributor
Posts: 58

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

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 Smiley Happy.
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 Smiley Sad...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 Smiley Sad.
Any idea?
Thanks

Daniele
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to DanieleTiles
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
SAS Super FREQ
Posts: 8,864

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

Posted in reply to DanieleTiles
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]
Contributor
Posts: 58

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

Posted in reply to Cynthia_sas
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 Smiley Happy.

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
Respected Advisor
Posts: 3,799

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

Posted in reply to DanieleTiles
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]
Contributor
Posts: 58

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

Posted in reply to data_null__
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
SAS Super FREQ
Posts: 8,864

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

Posted in reply to DanieleTiles
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
Contributor
Posts: 58

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

Posted in reply to Cynthia_sas
Hi Cynthia,
thank you very much for your answer! Finally, everything is explained Smiley Happy.
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 Smiley Happy
Ask a Question
Discussion stats
  • 7 replies
  • 220 views
  • 0 likes
  • 4 in conversation