Dear SAS-L -ers Especially the SQL ers.

I am trying to emulate LAST. processing using SQL.
I am creating new columns using the case/when and then sum these
columns (see simplified example below)
(BTW the monotonic() is very useful here  - equivalent of _n_ )

My problem is this....
How can I 'DROP' the temporary columns (totDent, totOpt, rowNum ) from
the output table. They were only created to allow me to calculate the
summarised columns  (totalDental,  totalOptical) which I do want.

Is it possible to do this within the same proc sql step? I dont want
to have to run any subsequent steps to tidy up an existing table.....I
eventually want to distribute this as a sql view , it  would therefore
be better as one 'UNIT' .

Note: productname and city... I dont want to summarise by every
combination of these.. just get the 'last' one stated per
customerid.


proc sql noprint ;
CREATE TABLE totalsBreakdown as
SELECT customerId , productName, city,  totalDental,  totalOptical

case when (hfact.servCode='D')
     then (hfact.payment)
     else (0)
end
label="Dental Claims" as totDent,

case when (hfact.servCode='O')
     then (hfact.payment)
     else (0)
end
label="Optical Claims" as totOpt ,

sum(calculated totDent) as totalDental ,
sum(calculated totOpt) as totalOptical ,

monotonic() as rowNum label="Row Num using monotonic" as rowNum

FROM claimlib.claimTable

WHERE clmDt >'01jan2010'd
GROUP BY customerId
HAVING max(rownum)=rownum
;;; QUIT;


regards, and thanks  Rob Ashmore

Rob,

I've never used it but, according to the author of the following
paper, you can use a drop statement as part of the create table
statement:

www.lexjansen.com/pharmasug/2006/technicaltechniques/tt03.pdf

Thus, if the paper's author is correct, you can use something like:

  create table want (drop=3Ddontwant) as
    select *
etc., etc.

HTH,
Art
--------------
On Jun 21, 10:18=A0am, rob <rob.ashm...@mbf.com.au> wrote:
> Dear SAS-L -ers Especially the SQL ers.
>
> I am trying to emulate LAST. processing using SQL.
> I am creating new columns using the case/when and then sum these
> columns (see simplified example below)
> (BTW the monotonic() is very useful here =A0- equivalent of _n_ )
>
> My problem is this....
> How can I 'DROP' the temporary columns (totDent, totOpt, rowNum ) from
> the output table. They were only created to allow me to calculate the
> summarised columns =A0(totalDental, =A0totalOptical) which I do want.
>
> Is it possible to do this within the same proc sql step? I dont want
> to have to run any subsequent steps to tidy up an existing table.....I
> eventually want to distribute this as a sql view , it =A0would therefore
> be better as one 'UNIT' .
>
> Note: productname and city... I dont want to summarise by every
> combination of these.. just get the 'last' one stated per
> customerid.
>
> proc sql noprint ;
> CREATE TABLE totalsBreakdown as
> SELECT customerId , productName, city, =A0totalDental, =A0totalOptical
>
> case when (hfact.servCode=3D'D')
> =A0 =A0 =A0then (hfact.payment)
> =A0 =A0 =A0else (0)
> end
> label=3D"Dental Claims" as totDent,
>
> case when (hfact.servCode=3D'O')
> =A0 =A0 =A0then (hfact.payment)
> =A0 =A0 =A0else (0)
> end
> label=3D"Optical Claims" as totOpt ,
>
> sum(calculated totDent) as totalDental ,
> sum(calculated totOpt) as totalOptical ,
>
> monotonic() as rowNum label=3D"Row Num using monotonic" as rowNum
>
> FROM claimlib.claimTable
>
> WHERE clmDt >'01jan2010'd
> GROUP BY customerId
> HAVING max(rownum)=3Drownum
> ;;; QUIT;
>
> regards, and thanks =A0Rob Ashmore