Creating Table with proc sql

[ali_far]

Hi all, 

I have a quick simple question.

data have;

input Id a b  ;

1 2  2

1 2  2

1 3  2

2 4  .

2 4  .

;

run;

Proc sql; 

Create table want as Id, a, sum(b) as new from have group by ID, a; quit;

This code does not account for the missing B. it means in "want table" it does not give the ID2.

If I merge the want table with the "have table'  my problem will solve. However, I was wonder if I can count for the missing directly in this code. So the data have should be like below:

data want;

input Id a new  ;

1 2  4

1 3  2

2 4  .

;

run;

Thanks

[SASKiwi · Accepted solution]

As far as I can see this gives you the "WANT" result you want:

Proc sql; 
Create table want as 
select Id, a, sum(b) as new 
from have 
group by ID, a; 
quit;

[ali_far]

Thanks for your reply.

I just tested the code here and it works for my sample data! You are right..
However, dont know why it does work ( I mean the way I want) for my real data..

I need to recheck other things perhaps...