Date:         Sat, 13 Aug 2005 20:39:15 -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: MODIFY with BY-groups?

The documentation does indicate that the BY statement is not supported for
a MODIFY statement which does not designate a transaction data set.

Here's a two-step solution which appears to work.

Test data:

   proc sql;
   create table bigtab as
   select sex as var1,
          age as var50,
          name as var51,
          weight as x,
          height as price
   from sashelp.class
   order by var1, var50, var51;
   quit;

First, derive a transactions data set:

   data transactions / view=transactions;
     retain firstx;
     drop   firstx;
     set bigtab;
     by var1 var50;
     if first.var50 then firstx=x;
     if last.var50 then do;
       price=price*2;
       x=firstx;
       output;
     end;
   run;

Second, make the in-place changes to the original data set:

   data bigtab;
     modify bigtab transactions;
     by var1 var50 var51;
     replace;
   run;

Notice that this depends on the existence of an additional BY variable
(VAR51 here), one which distinctly identifies each observation within the
VAR1-VAR50 group.

I made TRANSACTIONS a view, and it seems to work when, but that makes me a
little nervous, since the view is against a data set which is being changed
in the same step.

You probably want to index BIGTAB. But do you really have *50* BY variables?

The UPDATE statement in PROC SQL can also make changes in place. But it
looks to me like there too you would need to first create the table (or
view) of transactions, and then apply it via a subquery.

On Wed, 10 Aug 2005 07:01:28 -0700, NnlGggTuqPvt@SPAMMOTEL.COM wrote:

>I would like to do the following:
>
>data sql.bigtab;
>  retain firstx;
>  modify sql.bigtab;
>  by var1 var2 ... var50;
>  if first.var50 then firstx=x;
>  if last.var50 then do;
>    price=price*2;
>    x=firstx;
>    replace;
>  end;
>run;
>
>But with MODIFY this is not possible because BY is used with another
>meaning with MODIFY.
>Any idea how to solve this? I cannot use "SET" because I'm modifying a
>SAS/ACCESS table.
>
>Jens Martin Schlatter