/****************************************************************/
/* SAS SAMPLE LIBRARY */
/* */
/* NAME: SQLFUN03 */
/* TITLE: fun/interesting applications of PROC SQL. (fun03) */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN FORMAT CREATE VIEW PUT INPUT MIN SUM */
/* PROCS: SQL */
/* DATA: */
/* */
/* REF: */
/* MISC: this example was contributed by Jack Hamilton */
/* of Amdahl Corporation, via BITNET */
/* */
/* you can contribute your interesting samples. */
/* send internet email to KENT@UNX.SAS.COM or */
/* USmail to SAS Institute. */
/* */
/****************************************************************/
title1 'SAS SAMPLE LIBRARY, MEMBER(SQLFUN03)';
/* SQLDEMO SAS - describe data, create formats
I am in a chorus which is preparing for a trip next year. Not all of
the members can afford to pay all of their expenses (around $700), so I
conducted a survey to see how much additional fundraising the chorus as
a whole will have to do. I ended up with a data set that looked like
this:
Variable Length Format Meaning
SECTION $2 $SECTION. Voice part code
PAY $1 $PAY. Ability to pay code
I also wrote four formats using PROC FORMAT: */
proc format;
value $part
'B1', 'B2' = 'Bass/Baritone' 'T1', 'T2' = 'Tenor'
'A1', 'A2' = 'Alto' 'S1', 'S2' = 'Soprano'
'OT' = 'Other';
value $section
'B2' = 'Bass (B2)' 'B1' = 'Baritone (B1)'
'T2' = 'Tenor 2' 'T1' = 'Tenor 1'
'A2' = 'Alto 2' 'A1' = 'Alto 1'
'S2' = 'Soprano 2' 'S1' = 'Soprano 1'
'OT' = 'Other';
value $pay
'8' = 'More than $700' '7' = '$601-$700'
'6' = '$501-$600' '5' = '$401-$500'
'4' = '$301-$400' '3' = '$201-$300'
'2' = '$101-$200' '1' = '0-$100'
'B' = '(no answer)';
value $sortord
'B2' = '1' 'B1' = '2' 'T2' = '3' 'T1' = '4'
'A2' = '5' 'A1' = '6' 'S2' = '7' 'S1' = '8' 'OT' = '9';
*****; run;
/* SQLDEMO - read in sample data
I also wrote corresponding informats so I could do data entry using
PROC FSEDIT. A small sample of the dataset looked like this (not
using the formats): */
data survey;
attrib section length=$2;
attrib pay length=$1;
input section pay ;
cards;
B1 8
S2 3
S1 1
A2 4
A1 7
T2 5
T1 6
B2 2
T1 B (You know tenors - they're easily confused)
B2 6
S1 3
A1 5
;;;;
proc sql;
select * from survey;
title2 'GALA Chorus Festival Expense Survey';
*****; quit;
/* SQLDEMO - describe reports and first view:
I wanted three reports from the data: a count (and graph) by section,
and count (and graph) for each part, and the amount of money we would
have to raise. A complication is that voice parts are usually listed
in order by range, but the part names aren't alphabetical.
All three reports can be created using SQL.
First, I created a view which would display the SURVEY dataset sorted
in the correct order. The key here is to create new variables based
on the values of other variables. A new variable can be the same as
an old variable, but with a different format, or it can be calculated
from other variables.
The variables SECTION and PART refer to the same data, but SECTION uses
the $SECTION format, and PART uses the $PART format. The PART section
will take on fewer different values than the SECTION variable, because
the $PART format collapses the section values.
SORTORD refers to the same section data, but runs the value through the
$SORTORD format first. Note that you can't just format it differently,
as you did with PART and SECTION, because the sort works on the un-
formatted value. I chose to order the parts as BTAS (because I'm a
bass, so we come first), but you could also order SATB, or any other
order you wanted.
The CANPAY and DEFICIT fields are more complicated. Both are
calculated from the PAY variable. The DEFICIT variable is easier
to explain. If the person said they could pay $601-$700 dollars,
I assumed that they could really pay $600 (to be on the safe side -
better to set our sights too high than too low). If they left it ,
blank, I assumed we'd have to pay the whole $700. I used a CASE
expression to calculate the deficit number.
I could have calculated CANPAY the same way, but I decided to make
it more complicated. I took advantage of the fact that SAS treats
a true condition as the numeric value '1' and a false condition as
a numeric 0. I use the INPUT function on the character variable
PAY to return a number in the range 1-8 (or missing). If it's
missing, I assume they can't pay anything. Otherwise, I set the
value to $100 times the number, with a maximum value of $700. An
interesting artifact of the way I set this up is that CANPAY and
DEFICIT don't always sum to the same amount.
The variable BADCALC is also set up to show what happens when you
fail to account for the possibility of missing values. CANPAY is
calculated with a SUM function that will turn a missing value to 0;
BADCALC omits that precaution. */
proc sql;
create view going as
select
section as section format=$section.,
pay as pay format=$pay.,
section as part format=$part. label='Part',
put(section, $sortord.) as sortord label='Sort',
min(700, input(pay, 1.)*100) as badcalc,
min(700, sum(0, input(pay, 1.)*100)) as canpay
format=dollar8. label='Can Pay',
case pay
when 'B' then 700
when '8' then 0
when '7' then 100
when '6' then 200
when '5' then 300
when '4' then 400
when '3' then 500
when '2' then 600
when '1' then 700
end as deficit format=dollar8.0 label='Deficit'
from survey
order by sortord, section;
select section, part, pay, canpay, deficit, badcalc from going;
title2 'GALA Chorus Expense Survey with Calculated Fields';
*****; quit;
/* SQLDEMO - create summary view
The second view just works off the first view. Because a sum function
is used without a GROUP BY clause, one grand total is produced. */
proc sql;
create view cost as
select sum(700) as cost,
sum(deficit) as deficit,
sum(canpay) as canpay
from going;
select * from cost;
title2 'GALA Chorus Festival Expense Survey - Grand Totals';
*****; quit;
|
