/****************************************************************/ /*          S A S   S A M P L E   L I B R A R Y                 */ /*                                                              */ /*    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:                                                     */ /*                                                              */ /* SUPPORT: pmk                         UPDATE:                 */ /*     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 formatsI am in a chorus which is preparing for a trip next year.  Not all ofthe members can afford to pay all of their expenses (around $700), so Iconducted a survey to see how much additional fundraising the chorus asa whole will have to do.  I ended up with a data set that looked likethis:   Variable     Length     Format          Meaning   SECTION      $2         $SECTION.       Voice part code   PAY          $1         $PAY.           Ability to pay codeI 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 dataI also wrote corresponding informats so I could do data entry usingPROC FSEDIT.  A small sample of the dataset looked like this (notusing 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 wouldhave to raise.  A complication is that voice parts are usually listedin 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 sortedin the correct order.  The key here is to create new variables basedon the values of other variables.  A new variable can be the same asan old variable, but with a different format, or it can be calculatedfrom other variables.The variables SECTION and PART refer to the same data, but SECTION usesthe $SECTION format, and PART uses the $PART format.  The PART sectionwill take on fewer different values than the SECTION variable, becausethe $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 abass, so we come first), but you could also order SATB, or any otherorder you wanted.The CANPAY and DEFICIT fields are more complicated.  Both arecalculated from the PAY variable.  The DEFICIT variable is easierto 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 CASEexpression to calculate the deficit number.I could have calculated CANPAY the same way, but I decided to makeit more complicated.  I took advantage of the fact that SAS treatsa true condition as the numeric value '1' and a false condition asa numeric 0.  I use the INPUT function on the character variablePAY to return a number in the range 1-8 (or missing).  If it'smissing, I assume they can't pay anything.  Otherwise, I set thevalue to $100 times the number, with a maximum value of $700.  Aninteresting artifact of the way I set this up is that CANPAY andDEFICIT don't always sum to the same amount.The variable BADCALC is also set up to show what happens when youfail to account for the possibility of missing values.  CANPAY iscalculated 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 viewThe second view just works off the first view.  Because a sum functionis 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;