topic Re: summarizing data by proc sql in New SAS User

summarizing data by proc sql

asinusdk — Wed, 17 Oct 2018 22:35:53 GMT

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?

Re: summarizing data by proc sql

Reeza — Wed, 17 Oct 2018 22:39:02 GMT

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;

Re: summarizing data by proc sql

novinosrin — Wed, 17 Oct 2018 22:40:33 GMT

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;

Re: summarizing data by proc sql

PGStats — Thu, 18 Oct 2018 03:32:58 GMT

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;

Re: summarizing data by proc sql

asinusdk — Thu, 18 Oct 2018 14:36:22 GMT

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

Re: summarizing data by proc sql

asinusdk — Thu, 18 Oct 2018 14:59:11 GMT

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...

Re: summarizing data by proc sql

Reeza — Thu, 18 Oct 2018 15:06:01 GMT

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.