Date:         Fri, 26 May 2006 12:52:59 -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: PROC SQL Sum Into Macro Variable

On Fri, 26 May 2006 16:28:20 +0000, toby dunn <tobydunn@HOTMAIL.COM> wrote:

>Talbot ,
>
>Uhhh yeah you are, you are letting SQL do an implicit conversion from
>numeric to character for you when it shoves the summed value into the macro
>var.  It uses best12 formatting and normally this isnt a bad thing and
works

I think BEST12 would have worked (barely) for TMK in this case, but as I
recall SQL for some reason actually uses BEST8.

>fine.  However when you have a large number it does truncate things or when
>you have decimals and the number as a whole is large enough things get
>dropped off.  SO use a put function and put the variable into the macro var
>with the right numeric format.
>
>
>Data one ;
>  x = 16610879.18 ;
>  put x= ;
>run ;
>
>Proc sql noprint ;
>select x into : XVar
>from one ;
>quit ;
>
>%put %sysfunc( putn( &XVar , 12.2 ) ) ;
>
>
>Proc sql noprint ;
>select put( x , 12.2 ) into : XVar
>from one ;
>quit ;
>
>%put %sysfunc( putn( &XVar , 12.2 ) ) ;
>
>
>
>Toby Dunn
>
>
>
>
>
>From: Talbot Michael Katz <topkatz@MSN.COM>
>Reply-To: Talbot Michael Katz <topkatz@MSN.COM>
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: PROC SQL Sum Into Macro Variable
>Date: Fri, 26 May 2006 12:12:21 -0400
>
>Hi.
>
>Please have patience with me.  I think I asked a question like this
>before, but somehow I still haven't gotten it.
>
>When I sum into macro variable as follows:
>
>proc sql ;
>  reset noprint ;
>  select sum(x * (x ge &lim.))
>  into :sum_x_above_lim
>  from ds1
>  ;
>quit ;
>
>310  %put    sum_x_above_lim   =
>310! %sysfunc(putn(&sum_x_above_lim.,comma15.2))   ;
>sum_x_above_lim   =     16,610,879.00
>
>316  %put    sum_x_above_lim   =   &sum_x_above_lim. ;
>sum_x_above_lim   =   16610879
>
>
>When I sum the same into a data set:
>
>proc sql ;
>  create table sum_x_above_lim as
>  select sum(x * (x ge &lim.)) as
>  sum_x_above_lim
>  from ds1
>  ;
>quit ;
>
>and I print out the value I get: 16610879.18
>
>
>Why am I losing the part after the decimal point in the macro variable?
>
>
>--  TMK  --
>"The Macro Klutz"