Prev: Is it possible to empty the SAS log file without ending the
Next: puzzling SAS I/O question
From: saslearn chicago on 29 Jan 2010 12:28
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: Joe Matise on 29 Jan 2010 12:22
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 <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: Joe Matise on 29 Jan 2010 12:34
I'd venture to guess something like:
proc sql;
select *, case when n(x) = count(1) then sum(x) else . end as sum_X, case
when n(y) = count(1) then sum(y) else . end as sum_y
from t;
quit;

I think it's n() not nvl().
-Joe

On Fri, Jan 29, 2010 at 11:31 AM, SAS_learner <proccontents(a)gmail.com>wrote:

> is there an example for it please
>
> On Fri, Jan 29, 2010 at 9:28 AM, 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: Yu Zhang on 29 Jan 2010 12:33
here is a SQL solution, which might be what you are looking for.

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

proc sql;
select *,case when N(x)=count(*) then sum(x)
else . end as x_sum,
case when N(y)=count(*) then sum(y)
else . end as y_sum
from t;
quit;

On Fri, Jan 29, 2010 at 11:18 AM, 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: SAS_learner on 29 Jan 2010 12:31
is there an example for it please

On Fri, Jan 29, 2010 at 9:28 AM, 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
>>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Is it possible to empty the SAS log file without ending the
Next: puzzling SAS I/O question