Sample 25439: Demonstrates CASE statement with PROC SQL
 |  |  |  |  |
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This sample is from the SAS Sample Library. For additional information refer to SAS Help and Online Documentation.
/****************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: SQLFUN03 */
/* TITLE: Demonstrates CASE Statement with PROC SQL */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN FORMAT CREATE VIEW PUT INPUT MIN SUM */
/* PROCS: SQL */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: */
/* */
/****************************************************************/
/* 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;
/* 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;
title2 'GALA Chorus Festival Expense Survey';
select * from survey;
quit;
/* 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;
title2 'GALA Chorus Expense Survey with Calculated Fields';
select section, part, pay, canpay, deficit, badcalc from going;
quit;
/* 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;
title2 'GALA Chorus Festival Expense Survey - Grand Totals';
select * from cost;
quit;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
GALA Chorus Festival Expense Survey
section pay
------------
B1 8
S2 3
S1 1
A2 4
A1 7
T2 5
T1 6
B2 2
T1 B
B2 6
S1 3
A1 5
GALA Chorus Expense Survey with Calculated Fields
section Part pay Can Pay Deficit badcalc
--------------------------------------------------------------------------
Bass (B2) Bass/Baritone $501-$600 $600 $200 600
Bass (B2) Bass/Baritone $101-$200 $200 $600 200
Baritone (B1) Bass/Baritone More than $700 $700 $0 700
Tenor 2 Tenor $401-$500 $500 $300 500
Tenor 1 Tenor $501-$600 $600 $200 600
Tenor 1 Tenor (no answer) $0 $700 700
Alto 2 Alto $301-$400 $400 $400 400
Alto 1 Alto $401-$500 $500 $300 500
Alto 1 Alto $601-$700 $700 $100 700
Soprano 2 Soprano $201-$300 $300 $500 300
Soprano 1 Soprano 0-$100 $100 $700 100
Soprano 1 Soprano $201-$300 $300 $500 300
GALA Chorus Festival Expense Survey - Grand Totals
cost deficit canpay
----------------------------
8400 4500 4900
This example demonstrates how to use CASE statement with PROC SQL.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL |
| Date Modified: | 2005-08-24 16:06:32 |
| Date Created: | 2005-05-23 13:53:53 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |