topic Re: How to summarise monthly observations relative to a particular date? in SAS Programming

How to summarise monthly observations relative to a particular date?

bd_user_10 — Tue, 28 Aug 2018 05:42:22 GMT

I have the following data:

SEDOL	Date	TNUM	TVOL	DVOL	relday
4296098	2002-06-15	.	1	0.92	-54
4296098	2002-06-28	.	.	.	-41
4296098	2002-07-11	.	0.33	0.35	-28
4296098	2002-07-15	.	0.33	0.35	-24
4296098	2002-08-08	.	0.93	0.72	0
4296098	2002-08-08	.	1	1	7
4296098	2002-08-15	0.33	0.55	0.65	15
4296098	2002-08-30	.	.	.	35
4296098	2002-10-04	0.9	0.88	0.82	54
4296098	2002-11-27	.	.	.	67
4296098	2003-02-02	0.7	1	1	72

I want the following:

SEDOL	TNUM	TVOL	DVOL	Month
4296098	.	1	0.92	-2
4296098	.	0.66	0.7	-1
4296098	.	0.93	0.72	0
4296098	0.33	0.55	0.65	1
4296098	0.9	0.88	0.82	2
4296098	0.7	1	1	3

Thanks in advance for your help.

Re: How to summarise monthly observations relative to a particular date?

PeterClemmensen — Tue, 28 Aug 2018 05:46:13 GMT

I don't follow, please be more specific about how the logic in your desired result.

Re: How to summarise monthly observations relative to a particular date?

bd_user_10 — Tue, 28 Aug 2018 05:57:37 GMT

Basically, this is what I need:

sum TNUM, TVOL and DVOL for the following period

if relday -60 to -30 then month is -2

if relday -30 to -1 is month is -1

if relday 0 then month is 0

if relday 1 to 30 then month is 1

if relday 30 to 60 then month is 2

I have many sedol, and I want to do the same for all. I hope this make sense.

Re: How to summarise monthly observations relative to a particular date?

Kurt_Bremser — Tue, 28 Aug 2018 09:42:39 GMT

Define a value format with a range and use that:

proc format lib=work;
value relday
  -60 -< -30 = -2
  -30 -< 0 = -1
  0 = 0
  0 <- 30 = 1
  30 <- 60 = 2
  other = .
;
run;

data test;
input x;
y = input(put(x,relday.),best.);
cards;
-61
-60
-35
-30
-15
0
1
30
60
65
;
run;

Re: How to summarise monthly observations relative to a particular date?

ballardw — Tue, 28 Aug 2018 15:11:02 GMT

Or perhaps you could provide the actual date you are comparing. Use of INTCK function should provide the result directly.