Prev: Is it possible to empty the SAS log file without ending the
Next: puzzling SAS I/O question
From: xlr82sas on 29 Jan 2010 21:08
HI

I like Joe's solution

Minor simplification. You don't need the _n_=1 by 1?

data suma;
xsum=0;
ysum=0;
do until (eof);
set t end=eof;
xsum = xsum+x;
ysum = ysum+y;
end;
eof=0;
do until (eof);
set t end=eof;
output;
end;
run;







On Jan 29, 9:22 am, snoopy...(a)GMAIL.COM (Joe Matise) wrote:
> Data step will be easier.
>
> data t;
>   input t $2.  x y ;
> datalines;
> A  20   30
> B  30   20
> C  30   .
> ;;;;
> Run ;
>
> data sum;
> xsum=0;
> ysum=0;
> do _n_ = 1 by 1 until (eof);
>     set t end=eof;
>     xsum = xsum+x;
>     ysum = ysum+y;
> end;
> eof=0;
> do _n_ = 1 by 1 until (eof);
>     set t end=eof;
>     output;
> end;
> run;
>
> The problem in SQL is the sum function intentionally disregards missings, so
> it's a bit easier to do it in data step where you can use simple + operator.
>
> -Joe
>
> On Fri, Jan 29, 2010 at 11:18 AM, SAS_learner <procconte...(a)gmail.com>wrote:
>
>
>
> > data t;
> >   input t $2.  x y ;
> > A  20   30
> > B  30   20
> > C  30   .
> > ;;;
> > Run ;
>
> > Output dataset is sum of X and Y
>
> > T  X    y    X_Sum    Y_Sum
> > A 20  30     80          .
> > B 30  20     80
> > C 30          80
>
> > Hello all I am doing a simple sum using proc Sql using sum function, it is
> > giving me required results as expected but because of special purposes I
> > want to make my total missing if one of the (A B C ) are missing  as
> > missing. I know I can do this simply by transposing and adding them and put
> > it back. What I am looking is if it is possible to do with in proc sql step
> > or a data step without transpose and merging the datasets.
>
> > thanks
> > SL- Hide quoted text -
>
> - Show quoted text -

From: S=?ISO-8859-1?Q?=C3=B8ren?= Lassen on 1 Feb 2010 01:23
Indeed, you can. But unless you are doing Pass-through SQL calls to
Oracle (or another server that supports the NVL function), you will get
an error message. There is no NVL funtion in SAS, to the best of my
knowledge. Instead you should use the SQL standard COALESCE function,
which is, by the way, also available in Oracle and some (most, I think)
other SQL dialects.

The NVL/COALESCE function, however, does the exact opposite of what
SAS_learner wanted to accomplish. SL wanted to create null values,
not get rid of them.

Regards,
Søren

On Fri, 29 Jan 2010 12:28:16 -0500, saslearn chicago <sasswamy(a)GMAIL.COM>
wrote:

>You can try the NVL function in PROC sql
>
>On Fri, Jan 29, 2010 at 12:18 PM, SAS_learner
<proccontents(a)gmail.com>wrote:
>
>> data t;
>> input t $2. x y ;
>> A 20 30
>> B 30 20
>> C 30 .
>> ;;;
>> Run ;
>>
>> Output dataset is sum of X and Y
>>
>> T X y X_Sum Y_Sum
>> A 20 30 80 .
>> B 30 20 80
>> C 30 80
>>
>> Hello all I am doing a simple sum using proc Sql using sum function, it
is
>> giving me required results as expected but because of special purposes I
>> want to make my total missing if one of the (A B C ) are missing as
>> missing. I know I can do this simply by transposing and adding them and
put
>> it back. What I am looking is if it is possible to do with in proc sql
step
>> or a data step without transpose and merging the datasets.
>>
>> thanks
>> SL
>>
From: Sigurd Hermansen on 1 Feb 2010 10:44
SL:
Yu Zhang's solution has many of the features that I'd prefer in a SQL query. I'll include this solution as one that perhaps will be easier to follow so long as one knows SAS logical values, since it doesn't require one to know the subtle distinction between count(*) and n(x):

data t;
input t $2. x y ;
datalines;
A 20 30
B 30 20
C 30 .
;;;
Run ;

proc sql;
select t,x,y,
case when sum(x IS NULL) then .
else sum(x)
end as sumx,
case when sum(y IS NULL) then .
else sum(y)
end as sumy
from t
;
quit;

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of SAS_learner
Sent: Friday, January 29, 2010 12:18 PM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Simple sum in proc sql

data t;
input t $2. x y ;
A 20 30
B 30 20
C 30 .
;;;
Run ;

Output dataset is sum of X and Y

T X y X_Sum Y_Sum
A 20 30 80 .
B 30 20 80
C 30 80

Hello all I am doing a simple sum using proc Sql using sum function, it is
giving me required results as expected but because of special purposes I
want to make my total missing if one of the (A B C ) are missing as
missing. I know I can do this simply by transposing and adding them and put
it back. What I am looking is if it is possible to do with in proc sql step
or a data step without transpose and merging the datasets.

thanks
SL
First  |  Prev  | 
Pages: 1 2
Prev: Is it possible to empty the SAS log file without ending the
Next: puzzling SAS I/O question