## How to divide a variable by choosing time?

Solved
Regular Contributor
Posts: 166

# How to divide a variable by choosing time?

Hello everybody,

I want to divide each Intradayvolume observation by aggregated Dailyvolume variable in a five days before today. I mean SAS should considere today Intradayvolume as nominatore and divides it by summation of Dailyvolumes observations of five days before today as the denominator. On the other hands, I want to calculate adjusted_volume as I show below:

 name date CountedVOLUME DailyVolume adjusted_volume A 1 10 A 1 11 21 A 2 12 A 2 13 A 2 14 39 A 3 15 15 A 4 16 16 A 5 17 A 5 18 A 5 19 A 5 20 74 A 6 21 0.127272727 A 6 22 0.133333333 etc etc etc etc etc

I use codes which are shown below to calculate the Division. ‌But this code just divides the nominator by Dailyvolume in previous day.

``````*Adjusted intraday volume;
do until(last.TRD_STCK_CD);
do until(last.TRD_EVENT_DT);
set sampledata_sumvolso;
by TRD_STCK_CD TRD_EVENT_DT;

if first.TRD_STCK_CD then
n=0;

if first.TRD_EVENT_DT then
n+1;

if n>1 then
do;
end;

if last.TRD_EVENT_DT then
temp=dailyvolume;
output;
end;
end;
drop temp n;
run;

``````

Here is a sample of my data:

``````data WORK.SAMPLEDATA_SUMVOLSO;
infile datalines dsd truncover;
input TRD_STCK_CD:\$15. TRD_PR:32. TRD_TUROVR:14. TRD_EVENT_DT:DATE9. TRD_EVENT_TM:TIME5. TRD_EVENT_ROUNDED:32. TRD_EVENT_ROUFOR:\$5. CountedVOLUME:32. DailyVolume:32. IntradayVolume:32.;
format TRD_TUROVR 14. TRD_EVENT_DT DATE9. TRD_EVENT_TM TIME5.;
label TRD_STCK_CD="TRD_STCK_CD" TRD_PR="TRD_PR" TRD_TUROVR="TRD_TUROVR" TRD_EVENT_DT="TRD_EVENT_DT";
datalines4;
BALI1,850,9260,24MAR2008,9:14,34200,9:30,7871000,,
BALI1,850,2000,23MAR2008,9:15,34200,9:30,1700000,,
BALI1,850,10000,22MAR2008,9:15,34200,9:30,8500000,,
BALI1,850,6000,21MAR2008,9:15,34200,9:30,5100000,,
BALI1,850,10000,20MAR2008,9:29,34200,9:30,8500000,31671000,31671000
BANK1,1164,10729,20MAR2008,9:38,36000,10:00,12488556,,12488556
BANK1,1148,2000,21MAR2008,11:24,41400,11:30,2296000,,
BANK1,1147,1575,22MAR2008,11:24,41400,11:30,1806525,16591081,4102525
BHMN1,1013,1500,14MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,15MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,16MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,17MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,18MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,19MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1000,20MAR2008,9:00,34200,9:30,1013000,,
BHMN1,1013,450,21MAR2008,9:00,34200,9:30,455850,,
BHMN1,1013,1500,22MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,23MAR2008,9:00,34200,9:30,1519500,,
BHMN1,1013,1500,24MAR2008,9:04,34200,9:30,1519500,,
BHMN1,1013,1500,25MAR2008,9:04,34200,9:30,1519500,,
BHMN1,1013,601,26MAR2008,9:05,34200,9:30,608813,,
BHMN1,1013,697,27MAR2008,9:06,34200,9:30,706061,,
BHMN1,1013,1500,28MAR2008,9:08,34200,9:30,1519500,,
BHMN1,1013,1500,29MAR2008,9:08,34200,9:30,1519500,,
BHMN1,1013,1500,12MAR2008,9:09,34200,9:30,1519500,,
BHMN1,1013,1500,13MAR2008,9:16,34200,9:30,1519500,,
BHMN1,1013,1500,14MAR2008,9:22,34200,9:30,1519500,,25576224
BHMN1,1013,1500,15MAR2008,9:41,36000,10:00,1519500,,1519500
BHMN1,1013,1500,16MAR2008,10:13,37800,10:30,1519500,,
BHMN1,1013,1500,17MAR2008,10:13,37800,10:30,1519500,,3039000
BHMN1,1013,1500,18MAR2008,11:25,41400,11:30,1519500,,1519500
BHMN1,1013,1500,19MAR2008,11:51,43200,12:00,1519500,33173724,1519500
CHML1,12000,745,20MAR2008,9:00,34200,9:30,8940000,,
CHML1,12000,82,21MAR2008,9:00,34200,9:30,984000,,
CHML1,11998,206,22MAR2008,9:07,34200,9:30,2471588,,
CHML1,11998,414,23MAR2008,9:09,34200,9:30,4967172,,
CHML1,11998,348,24MAR2008,9:12,34200,9:30,4175304,,
CHML1,11996,82,25MAR2008,9:15,34200,9:30,983672,,
CHML1,11996,1240,26MAR2008,9:16,34200,9:30,14875040,,
CHML1,11996,414,27MAR2008,9:17,34200,9:30,4966344,,
CHML1,11970,1000,28MAR2008,9:21,34200,9:30,11970000,,
CHML1,11964,158,29MAR2008,9:22,34200,9:30,1890312,,
CHML1,11990,1946,20MAR2008,9:23,34200,9:30,23332540,,79555972
CHML1,11851,500,19MAR2008,10:42,39600,11:00,5925500,,
CHML1,11852,1054,18MAR2008,10:44,39600,11:00,12492008,,
CHML1,11900,2000,17MAR2008,10:56,39600,11:00,23800000,,42217508
CHML1,11913,244,16MAR2008,11:08,41400,11:30,2906772,,
CHML1,11913,56,15MAR2008,11:09,41400,11:30,667128,,
CHML1,11968,944,14MAR2008,11:13,41400,11:30,11297792,,
CHML1,11968,2056,13MAR2008,11:27,41400,11:30,24606208,,
CHML1,11987,380,14MAR2008,11:29,41400,11:30,4555060,,44032960
CHML1,11988,620,11MAR2008,11:30,43200,12:00,7432560,,
CHML1,11981,1663,12MAR2008,11:52,43200,12:00,19924403,,
CHML1,11981,765,24MAR2008,11:52,43200,12:00,9165465,202328868,36522428
CRBN1,1486,1700,22MAR2008,9:00,34200,9:30,2526200,,
CRBN1,1485,3353,24MAR2008,9:21,34200,9:30,4979205,7505405,7505405
DFRB1,7450,124,24MAR2008,10:54,39600,11:00,923800,,923800
DFRB1,7450,132,24MAR2008,11:16,41400,11:30,983400,1907200,983400
DJBR1,7491,3000,24MAR2008,10:06,37800,10:30,22473000,22473000,22473000
DRZK1,14890,72,24MAR2008,11:58,43200,12:00,1072080,1072080,1072080
FIBR1,2846,100,24MAR2008,11:17,41400,11:30,284600,284600,284600
FKAS1,3584,5173,20MAR2008,9:15,34200,9:30,18540032,,
FKAS1,3584,3000,21MAR2008,9:15,34200,9:30,10752000,,
FKAS1,3585,1827,22MAR2008,9:15,34200,9:30,6549795,,35841827
FKAS1,3585,183,23MAR2008,9:35,36000,10:00,656055,,
FKAS1,3585,3200,24MAR2008,9:35,36000,10:00,11472000,,12128055
FKAS1,3539,2000,25MAR2008,11:02,41400,11:30,7078000,,
FKAS1,3538,8000,26MAR2008,11:02,41400,11:30,28304000,,
FKAS1,3538,2000,27MAR2008,11:02,41400,11:30,7076000,,
FKAS1,3537,8000,28MAR2008,11:02,41400,11:30,28296000,,
FKAS1,3537,2000,29MAR2008,11:02,41400,11:30,7074000,,
FKAS1,3535,8000,20MAR2008,11:02,41400,11:30,28280000,,
FKAS1,3535,2000,19MAR2008,11:03,41400,11:30,7070000,,
FKAS1,3535,6617,18MAR2008,11:03,41400,11:30,23391095,,
FKAS1,3533,100,17MAR2008,11:03,41400,11:30,353300,,
FKAS1,3533,1283,16MAR2008,11:07,41400,11:30,4532839,,
FKAS1,3533,2763,15MAR2008,11:08,41400,11:30,9761679,,
FKAS1,3529,10000,14MAR2008,11:15,41400,11:30,35290000,,
FKAS1,3516,10000,13MAR2008,11:17,41400,11:30,35160000,,
FKAS1,3516,10000,12MAR2008,11:19,41400,11:30,35160000,,
FKAS1,3516,39,11MAR2008,11:19,41400,11:30,137124,,
FKAS1,3515,2000,19MAR2008,11:25,41400,11:30,7030000,,
FKAS1,3515,198,24MAR2008,11:25,41400,11:30,695970,,
FKAS1,3515,9802,20MAR2008,11:25,41400,11:30,34454030,,299144037
FKAS1,3515,4000,21MAR2008,11:48,43200,12:00,14060000,,
FKAS1,3513,6000,22MAR2008,11:52,43200,12:00,21078000,,
FKAS1,3514,2000,23MAR2008,11:52,43200,12:00,7028000,,
FKAS1,3514,1085,24MAR2008,11:52,43200,12:00,3812690,,
FKAS1,3500,1000,25MAR2008,11:56,43200,12:00,3500000,396592609,49478690
FOLD1,3198,390,24MAR2008,9:09,34200,9:30,1247220,,1247220
FOLD1,3197,2000,27MAR2008,10:31,39600,11:00,6394000,,
FOLD1,3191,2000,28MAR2008,10:32,39600,11:00,6382000,,
FOLD1,3190,4000,20MAR2008,10:33,39600,11:00,12760000,,
FOLD1,3190,542,18MAR2008,10:33,39600,11:00,1728980,,
;;;;``````

Important varaibles:

TRD_STCK_CD = name;

TRD_EVENT_TM = time;

CountedVOLUME= Intraday volume as a nominator;

DailyVolume = As a denominator;

TRD_EVENT_ROUFOR = The time variable (TRD_EVENT_TM) that has been rounded to half an hour periods;

How can I do that?

Accepted Solutions
Solution
‎08-16-2017 08:40 AM
Regular Contributor
Posts: 166

## Re: How to divide a variable by choosing time?

[ Edited ]

The solution is:

``````*New adjusted intraday volume for five days;
set Sampledata_SumVolSo02;
by TRD_STCK_CD TRD_EVENT_DT;
retain dv_1 dv_2 dv_3 dv_4 cnt 0 dv_sum;
if first.TRD_STCK_CD then do;
cnt=0;
dv_1=0;
dv_2=0;
dv_3=0;
dv_4=0;
dv_sum=0;
end;
if last.TRD_EVENT_DT then do;
cnt=cnt+1;
if cnt=1 then dv_4=dailyvolume;
else if cnt=2 then dv_3=dailyvolume;
else if cnt=3 then dv_2=dailyvolume;
else if cnt=4 then dv_1=dailyvolume;
else do;
dv_sum=dailyvolume+dv_1+dv_2+dv_3+dv_4;
dv_4=dv_3;
dv_3=dv_2;
dv_2=dv_1;
dv_1=dailyvolume;
end;
end;
run;``````

https://stackoverflow.com/questions/45699911/how-can-i-calculate-the-division-using-sas/45709253#457...

All Replies
Super User
Posts: 23,323

## Re: How to divide a variable by choosing time?

1. Isolate the denominator values by calculating them and storing them in a separate data set

2. Merge the denomintors in so it's at each row

3. Do the division

Yes, this can be done in a single Data step with a DoW loop, but it seems like that may be too complex for you to manipulate for future calculations. This method will work, even if it's not super efficient.

Regular Contributor
Posts: 166

## Re: How to divide a variable by choosing time?

Would you mind telling me how can I calculate the denaminator?
Super User
Posts: 23,323

## Re: How to divide a variable by choosing time?

Do you have SAS/ETS? If so look at PROC EXPAND. If not, use a temporary array. There are links below that illustrate each approach.

Using PROC TIMESERIES to fill in missing dates and then proc expand to calculate summary statistics. Review the docs to see what you need to change for the sum.

https://gist.github.com/statgeek/07a3708dee1225ceb9d4aa75daab2c52

Temporary Array Approach

https://gist.github.com/statgeek/27e23c015eae7953eff2

Regular Contributor
Posts: 166

## Re: How to divide a variable by choosing time?

There is another way to calculate the denominator:
I can use codes which I have shown before for five time. It means that I can rewrite codes by n>2, n>3, n>4, n>5 and in final step I can aggregate the variables. But there is two problems. First, how can I change "if first.TRD_EVENT_DT then" statement to second, third, fourth and fifth day. And finally, how can I calculate summation variables which are made?
Super User
Posts: 23,323

## Re: How to divide a variable by choosing time?

[ Edited ]

Here's why I don't recommend that approach. It's beyond your current skills and you cannot modify it yourself. If you're willing to put in the time to learn and understand that type of coding it would be different, otherwise, I (or someone on here) pretty much have to write the code for you, and that's what you're getting a publication/degree/paid for, not me

Feel free to go down that road, ultimately it's your code, program and degree.

EDIT: there's also more ways that I've listed here, there are many ways, I'm trying to point you to the simplistic approach so you can do this yourself. Obviously my approach or comments may not be what you want to hear, but it's how I approach the forum and questions.

Regular Contributor
Posts: 166

## Re: How to divide a variable by choosing time?

Please do not mention that I want you write a whole code. Yes, you are right, I am writing my graduate program in economics and I have codes which I just want to correct them with your help, not writing a program from A to Z.
Super User
Posts: 23,323

## Re: How to divide a variable by choosing time?

I never said that. I said I was trying to outline the simplest approach so that you could do it yourself.

I live in Alberta, and we have a phrase, "You can eat a whole cow a bite at a time". I was trying to help you break the problem down to bite size pieces. Tackling everything at once is possible but as your projects get bigger and bigger it doesn't work.

Solution
‎08-16-2017 08:40 AM
Regular Contributor
Posts: 166

## Re: How to divide a variable by choosing time?

[ Edited ]

The solution is:

``````*New adjusted intraday volume for five days;
set Sampledata_SumVolSo02;
by TRD_STCK_CD TRD_EVENT_DT;
retain dv_1 dv_2 dv_3 dv_4 cnt 0 dv_sum;
if first.TRD_STCK_CD then do;
cnt=0;
dv_1=0;
dv_2=0;
dv_3=0;
dv_4=0;
dv_sum=0;
end;
if last.TRD_EVENT_DT then do;
cnt=cnt+1;
if cnt=1 then dv_4=dailyvolume;
else if cnt=2 then dv_3=dailyvolume;
else if cnt=3 then dv_2=dailyvolume;
else if cnt=4 then dv_1=dailyvolume;
else do;
dv_sum=dailyvolume+dv_1+dv_2+dv_3+dv_4;
dv_4=dv_3;
dv_3=dv_2;
dv_2=dv_1;
dv_1=dailyvolume;
end;
end;
run;``````

https://stackoverflow.com/questions/45699911/how-can-i-calculate-the-division-using-sas/45709253#457...

☑ This topic is solved.