Prev: Quoted macro variable not resolved when literal prefix
Next: fitting a regression line
From: rob on 21 Jun 2010 10:18
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
From: Arthur Tabachneck on 21 Jun 2010 18:52
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=dontwant) as
select *
etc., etc.

HTH,
Art
--------------
On Jun 21, 10:18 am, rob <rob.ashm...(a)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  - 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

 | 
Pages: 1
Prev: Quoted macro variable not resolved when literal prefix
Next: fitting a regression line