## How to adjust a variable via dividing it by the sum of observations in previous days?

Solved
Regular Contributor
Posts: 166

# How to adjust a variable via dividing it by the sum of observations in previous days?

[ Edited ]

Hello everybody,

I want to write a code to run the formula which is shown below:

And I use codes which are shown below:

proc sort data=sampledata_sumvolso02 out=sampledata_sumvolso02;
by TRD_STCK_CD TRD_EVENT_DT;
run;

do until(last.TRD_STCK_CD);
do until(last.TRD_EVENT_DT);
set sampledata_sumvolso02;
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>7 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_SUMVOLSO02;
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,,
;;;;

Now, I want to divide Intradayvolume variable by aggregated  Dailyvolume variable in a week before today. It is important to specify for SAS to diagnoses days of a week and adapts it with the date variable in my dataset. Because the format of the date variable of my dataset is ddmmyy. I mean SAS should find which days of a week are today and aggregates Dailyvolume variable of a week before today as the denominator.

How can I run that formula?

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

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Here is a solution:

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;

REF.

All Replies
Super User
Posts: 23,342

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Can you post this as an example directly into your post? There are restrictions on using zip files in many organizations.

Super User
Posts: 13,358

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

How about providing sample data such that you only have 2 or 3 TRD_STCK and and 2 or 3 values associated TRD_EVENT_DT for each of those, ONLY the variables involved in the question, and what you expect the results for that much smaller example dataset to be.

Then you could paste that data step code directly into the post instead of an attachment.

Regular Contributor
Posts: 166

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Hello @ballardw

Here is another sample.

Super User
Posts: 7,941

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

[ Edited ]

I doubt that we need 50,000 observations. We definitely don't need ZIP files that users need to download.

Just post some sample data that demonstrates the issue you have. Make up the data if you want.  We don't need all the variables, just the ones that impact on the formula you are trying to figure out how to code.

Perhaps something like this.  Make sure to include more than one days worth of data. And perhaps more than one stock code.

data WORK.SAMPLEDATA_RT;
infile datalines dsd truncover;
length TRD_STCK_CD \$15 TRD_EVENT_DT 8 TRD_EVENT_TM 8 CountedVOLUME 8 ;
informat TRD_EVENT_DT DATE9. TRD_EVENT_TM TIME5.;
format TRD_EVENT_DT DATE9. TRD_EVENT_TM TIME5.;
input TRD_STCK_CD -- CountedVOLUME ;
datalines4;
BALI1,24MAR2008,9:14,7871000
BALI1,24MAR2008,9:15,1700000
BALI1,24MAR2008,9:15,8500000
;;;;

Calculate the formula by hand and post the results for the sample data.

Regular Contributor
Posts: 166

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Hello Tom.
I pasted 1000 obs. directly into your post.
Super User
Posts: 7,941

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Probably more than we need. What answer do you expect from those 1,000 observations?

Regular Contributor
Posts: 166

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

would you please tell me if my question is ambiguous?
Super User
Posts: 23,342

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

It's not ambigous, to be honest it's too much for a simple forum question. If you can simplify it down to two ticker symbols over two days with the expected results that may help. But to look over about 70 lines of code and 1000 data points and not see what you want is a bit difficult and maybe more than someone has time for at the moment. So you'll likely have to wait longer for an answer. The more simple you can make your question, the faster you'll get a response. As I mentioned, if you simplify it down or even break it into sections you're more likely to get a faster response. But now we have to first understand your formula - it's been a while since I read math notations, understand your code and understand your data...it's more than 5 minutes that I usually spend on most questions. It's not that you won't get an answer, just that you'll have to wait a bit.

Super User
Posts: 7,941

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

[ Edited ]

Does the data file contain data for every day?  Or can we just assume that it can use the previous day's total?

Please fill in the missing information.

What variable indicates the DAY?  Looks like it is TRD_EVENT_DT.

What variable has the volumes to be totaled? Looks like it is CountedVOLUME.

What is the meaning of the extra TRD_STICK_CD variable in your BY groups?

Assuming that TRD_STICK_CD is something that indicates a different type of volume is being calculated shouldn't your BY statement be:

by TRD_STICK_CD TRD_EVENT_DT;

You wouldn't want to compare the volume of APPLES from today to the volume of BANANAS from yesterday.

Regular Contributor
Posts: 166

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Hello,
The day variable is TRD_EVENT_DT.
The CountedVOLUME is TRD_PR *TRD_TUROVR.
The TRD_STICK_CD is a name variable.
Solution
‎08-16-2017 09:11 AM
Regular Contributor
Posts: 166

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

Here is a solution:

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;

REF.

Super User
Posts: 23,342

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

https://stackoverflow.com/questions/45699911/how-can-i-calculate-the-division-using-sas
Regular Contributor
Posts: 166

## Re: How to adjust a variable via dividing it by the sum of observations in previous days?

[ Edited ]

@Reeza, I am so grateful for your kindness.