How do i Sum a column based on its year (2 different years in the same table)?

StickyRoll — Fri, 24 Jan 2025 08:10:23 GMT

Data mockup;

ID='1';

year=2023;

amt=1;

output;

ID='2';

year=2023;

amt=2;

output;

ID='3';

year=2024;

amt=100;

output;

ID='4';

year=2024;

amt=200;

output;

run;

I know i can easily sum up a column by using Proc SQL sum. However, in this case, i wish to achieve the following item:
Get the Difference of sum between the total of 2024 - total of 2023. Instead of separating 2 PROC SQL just to sum where year = xxxx, is there a quicker way?

I can only think of this:

Proc SQL;

create table out2024 as
select *
, sum(amt) as amt_2024
from mockup
where year=2024;
quit;



Proc SQL;
create table out2023 as
select *
, sum(amt) as amt_2023
from mockup
where year=2023;
quit;



proc sql;
create table diff as
select a2024.* 
,  b2023.*
from out2024 a2024 left join
out2023 b2023
on a2024.ID = b2023.ID;
quit;



data Diff_final;
set diff;
totaldiff = amt_2024-amt2023;
run;

I am sure there is a quicker way for achieving the same thing. Seeking your advice.

Re: How do i Sum a column based on its year (2 different years in the same table)?

ChrisNZ — Fri, 24 Jan 2025 09:57:49 GMT

Untested code. Something like this should work.

proc sql;
  select sum(AMT * (YEAR=2023)) - sum(AMT * (YEAR=2024)) as DIFF
  from MOCKUP;
quit;

The amount is nullified when the year test is not true.

topic How do i Sum a column based on its year (2 different years in the same table)? in SAS Programming

How do i Sum a column based on its year (2 different years in the same table)?

Re: How do i Sum a column based on its year (2 different years in the same table)?