How to divide a variable by choosing time?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 166
Accepted Solution

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:

 

namedateCountedVOLUMEDailyVolume adjusted_volume
A110  
A11121 
A212  
A213  
A21439 
A31515 
A41616 
A517  
A518  
A519  
A52074 
A621 0.127272727
A622 0.133333333
etcetcetcetcetc

 

 

 

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;
data sampledata_adjvol;
     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;
                           if not missing(IntradayVolume) then
                                adjusted_volume=divide(IntradayVolume,temp);
                                else call missing(adjusted_volume);
                     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
DADE1,1685,2972,24MAR2008,10:04,37800,10:30,5007820,,5007820
DADE1,1632,2000,20MAR2008,11:49,43200,12:00,3264000,,
DADE1,1631,5000,21MAR2008,11:49,43200,12:00,8155000,,
DADE1,1630,3000,22MAR2008,11:49,43200,12:00,4890000,,
DADE1,1630,2000,23MAR2008,11:50,43200,12:00,3260000,,
DADE1,1630,8000,24MAR2008,11:50,43200,12:00,13040000,37616820,32609000
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?

Thanks is advance.


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

Re: How to divide a variable by choosing time?

[ Edited ]
Posted in reply to aminkarimid

The solution is:

 

*New adjusted intraday volume for five days;
data sampledata_adjvol (drop=cnt dv_:);
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;
adjusted_volume=countedvolume/dv_sum;
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...

View solution in original post


All Replies
Super User
Posts: 23,323

Re: How to divide a variable by choosing time?

Posted in reply to aminkarimid

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?

Posted in reply to aminkarimid

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 ]
Posted in reply to aminkarimid

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 Smiley Wink

 

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.
Nevertheless, thanks for your help.
Super User
Posts: 23,323

Re: How to divide a variable by choosing time?

Posted in reply to aminkarimid

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 ]
Posted in reply to aminkarimid

The solution is:

 

*New adjusted intraday volume for five days;
data sampledata_adjvol (drop=cnt dv_:);
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;
adjusted_volume=countedvolume/dv_sum;
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 217 views
  • 3 likes
  • 2 in conversation