Date:         Wed, 18 Apr 2007 22:30:31 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: SQL, update, case exists

On Tue, 17 Apr 2007 15:38:13 -0400, Karstein Rystad <k-rystad@ONLINE.NO> wrote:

>Hi!
>
>This is my headache: Opt in table a shall be updated whith Yes if type = dg
>or dd exists in b1 or b2 and the date suites. Row two in b1 destoy my code.
>
>data a;
>  input n $ Date_f 5. Date_t 5. Opt $3.;
>  datalines;
>00001 0701 0702
>00002 0601 0702
>00003 0701 0704
>00004 0612 0612
>00007 0604 0609
>00008 0703 0704
>00009 0611 0702
>00010 0609 0705
>;
>
>data b1;
>  input n $ type $2.;
>  datalines;
>00002 dg
>00002 dd
>00009 ss
>00010 ss
>;
>
>data b2;
>  input n $ type $2.;
>  datalines;
>00002 ss
>00004 bb
>00005 ee
>00008 dd
>00009 ss
>00007 dg
>;
>run;
>
>%macro test;
>  proc sql;
>  %do i=1 %to 2;
>    update a as a
>      set Opt = case (select type
>                        from b&i. as b
>                        where a.n=b.n)
>                  when 'dg' then 'Yes'
>                  when 'dd' then 'Yes'
>                  else Opt
>        end
>      where Date_f <= 0609 <= Date_t;
>  %end;
>  quit;
>%mend;
>%test;
>proc print data=a;
>run;
>
>/* This should be the result:
>      n      Date_f    Date_t    Opt
>
>    00001      701       702
>    00002      601       702     Yes
>    00003      701       704
>    00004      612       612
>    00007      604       609     Yes
>    00008      703       704
>    00009      611       702
>    00010      609       705
>*/

Here's a way. In addition to solving the immediate problem, it employs the
SQL UNION operator to avoid the need for macro code.

   proc sql;

   create view all_b as
    (select n, case type when 'dd' then 'dg' else type end as type
     from b1)
 union
    (select n, case type when 'dd' then 'dg' else type end as type
     from b2)
   ;

   update a
    set Opt = case (select type
                     from all_b as b
                     where a.n=b.n and type ='dg')
                   when 'dg' then 'Yes'
                   else Opt
                   end
    where Date_f <= 0609 <= Date_t;

  quit;