BookmarkSubscribeRSS Feed
StickyRoll
Fluorite | Level 6

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

 

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.

1 REPLY 1
ChrisNZ
Tourmaline | Level 20

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.

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 556 views
  • 2 likes
  • 2 in conversation