topic Re: Compute Net Holdings in SAS Programming

Compute Net Holdings

SidS — Tue, 09 Nov 2010 05:01:05 GMT

Hello,
I am a newbie to SAS and am trying to figure out the best way to solve this problem. I have a data set of option holdings:
Name Action Quantity Maturity
Joe Buy 100 Dec 12
Joe Buy 100 Dec 10
Joe Sell 50 Dec 10
Mary Buy 50 Dec 12

I want the result to have net current holdings:
Joe 100 Dec 12
Joe 50 Dec 10
Mary 50 Dec 12

Is there a clean and elegant way to write this?

Thanks,
Sid

Re: Compute Net Holdings

Ksharp — Tue, 09 Nov 2010 09:21:31 GMT

Hi.
You can use ' proc sort nodup; by name descending maturity;run;' to get it.

Re: Compute Net Holdings

Daryl — Tue, 09 Nov 2010 14:12:01 GMT

You could try something like this:

[pre]
/* create a sample data set*/
data holdings;
informat maturity date9.;
format maturity date9.;
input name $ action $ quantity maturity;
datalines;
Joe Buy 100 12-Dec-2010
Joe Buy 100 10-Dec-2010
Joe Sell 50 10-Dec-2010
Mary Buy 50 12-Dec-2010
;
run;

/* for Sell transactions, change the sign on the quantity to negative */
data holdings2;
set holdings;
if action = "Sell" then do;
quantity = -quantity;
end;
run;

/* Group the transactions by name and maturity date */
proc sort data=holdings2;
by name maturity;
run;

/* Add up the transactions for each group */
proc means data=holdings2 noprint;
by name maturity;
var quantity;
output out=holdings3 sum=;
run;

/* Print results */
proc print data=holdings3 noobs;
var name maturity quantity;
run;
[/pre]

Re: Compute Net Holdings

SPR — Tue, 09 Nov 2010 14:22:17 GMT

Hello Sid S,

If I correctly understood what you need, the following code produces the result:

data a;
input Name $ 1-4 Action $ 6-10 Quantity 11-13 Maturity $ 15-20;
datalines;
Joe Buy 100 Dec 12
Joe Buy 100 Dec 10
Joe Sell 50 Dec 10
Mary Buy 50 Dec 12
;
run;
/* Add minus sign to quantity for activity=sell */;
data a1;
set a;
if UPCASE(Action)="SELL" then quantity=-quantity;
run;
/* Summarizing Quantity by Name and Maturity */;
proc SQL;
create table r as
select Name, SUM(quantity) as Quantity, Maturity
from a1
group by Name, Maturity
;quit;

Sincerely,
SPR

Re: Compute Net Holdings

SidS — Wed, 10 Nov 2010 00:05:30 GMT

Thanks for your help. It really makes it easier to see this implementation using groups. Unfortunately my brain still think in C and that doesn't seem like the right approach for SAS.
BTW what is the advantage/disadvantage of using PROC SQL?

Re: Compute Net Holdings

Cynthia_sas — Wed, 10 Nov 2010 02:33:34 GMT

Hi:
This is one of those apples/oranges questions. It depends on what you're accessing data-wise, how big the tables are, whether you're joining or summarizing or doing sub-queries, etc, etc. You really have to benchmark. Do you want to optimize for I/O operations; for CPU; for ease of maintenance???

As a "for-instance", if you need to get both matches and non-matches out of a single join/merge operation, then you can do that in a DATA step program with one program, whereas with SQL you need one join to get the matches and a second join to get the non-matches.

For a shop that has programmers with a lot of SQL expertise, then PROC SQL is a good entree into using SAS. If a shop has a lot of legacy files (such as sequential mainframe files and/or database files) to read, join, and process, then "regular" DATA step programming might be a better alternative. It all depends.

If you search for user group papers on PROC SQL and DATA step, you will find lots of papers that talk about the pros and cons to PROC SQL versus other SAS approaches.

cynthia

Re: Compute Net Holdings

jen123 — Tue, 01 Jul 2014 21:11:08 GMT

I am hoping this is a simple question. I followed the advice on this thread to change a row of numbers from + to -. Would you know what is it I am missing on line 47?

44 Data ab_2;

45 Set ab;

46 if AccountType="total saved" then do;

47 _4Q14=_-2014Q4;

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,

LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.