topic Re: summing according to different criteria in SAS Programming

summing according to different criteria

aiste — Fri, 03 Jul 2020 15:49:37 GMT

Hello everyone,

I am struggling with a very simple problem but somehow I am not getting what I want to - to addition values in one column by creating another column, given several criteria.

I add a little extract of the dataset, in a very simplified version, we have three columns - jour (date), investor(a number of ID), position(a number).

I want to create another column named sumpos which would provide me with a sum of Position variable IF the investor is the same AND IF the date is the same - basically, daily positions.

The pieces of code I have tried are the following:

proc sql;
create table ptf_funds as
select
investor,
jour,
SUM(Position) as sumPosition
from funds_merged1
group by investor, jour;
quit;

but this code above does not add all the values - skips some of them and I did not manage to understand which and why.

Another way I have tried is the following:

data atrial_funds;
set funds_merged1;
if ((investor=investor) AND (jour=jour))then sum(Position)=sumPosition;
else sumPosition=sumPosition;
run;

But I am not sure if this would work and I get an error saying that I did not define the sum as an array.

If anyone could help me, this would be great!

Re: summing according to different criteria

Reeza — Fri, 03 Jul 2020 16:17:35 GMT

I'm fairly certain you just need a proc means, unless you want a running total in your data set?

proc sort data=funds_merged1;
by investor jour;
run;

proc means data=funds_merged N SUM;
by investor jour;
var position;
output out=want sum(position) = Position;
run;

If you do want a running total you want this then:

proc sort data=funds_merged1;
by investor jour;
run;

data want_running_total;
set funds_merged1;
by investor jour;
if first.jour then runningTotal = position;
else runningTotal + position;
run;

BY group processing allows you to group your analysis by different variables and you don't need to check for investor=investor. FYI - since SAS only processes a line at a time, investor=investor will always be true since it's referring to the same values.

Re: summing according to different criteria

s_lassen — Sat, 04 Jul 2020 14:24:45 GMT

What's wrong with your SQL query? Can you give an example of the data (datastep code) that does not sum correclty?