summarizing data by proc sql

[asinusdk]

data have
input year id v1 v2 v3;
cards;
1 1 . 1 20
1 1 . 1 30
1 1 . 1 10
2 1 . 1 10
3 1 . 1 10
3 1 . 1 20
1 2 . 1 20
1 2 . 2 30
2 2 . 1 20
2 2 . 1 20
;

From this data,

I want to summarize the number by id and year. What I want is... 

data want;
input year id v1_sum v2_sum v3_sum;
cards;
1 1 . 3 60
2 1 . 1 10
3 1 . 2 30
1 2 . 3 50 
2 2 . 2 40
3 2 . . .
; 

The code I tried is something like this:

proc sql;
create table sum_diag as
select person_id, year,
from diagnos
group by year
order by person_id, year; quit;

I couldn't get the result by year. There were multiple observations  per year for several rows. What should I do to get sum by year within a subject? 

[Reeza]

Any reason to not use a summary proc designed for analysis?

Either way, your code doesn't show any attempt at summarization, did you remove that portion before posting?

Otherwise, adding in your summary stats is all you need.

proc sql;
create table sum_diag as
select person_id, year, sum(v1) as sum_v1, sum(v2) as sum_v2
from diagnos
group by year
order by person_id, year; quit;

[asinusdk]

I actually tried this code, but the result showed the sum of all v1 frequency from all subjects and all years.

[novinosrin · Accepted solution]

proc sql;
create table sum_diag as
select id, year, sum(v1) as v1_sum,sum(v2) as  v2_sum ,sum(v3) as v3_sum
from diagnos
group by id,year
order by id, year; 
quit;

[PGStats]

You can include the combinations of year and id that do not occur in your data wit a cross join (cartesian product) and a left join:

proc sql;
create table want as
select a.year, b.id,
    sum(v1) as sumV1, sum(v2) as sumV2, sum(v3) as sumV3
from 
    (select unique year from have) as a cross join
    (select unique id from have) as b left join
    have as c on a.year=c.year and b.id=c.id
group by a.year, b.id
order by id, year;
quit;

[asinusdk]

ERROR: Column person_id could not be found in the table/view identified with the correlation
name b.
ERROR: Expression using equals (=) has components that are of different data types.

Thank you very much...But these errors came up...

[Reeza]

Is there a variable named person_id in the table? From your code you referred to it that way, but your example data shows a variable named ID? And do the variables have the same type.