turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to divide a variable by choosing time?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-15-2017 09:48 AM

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;
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aminkarimid

08-16-2017 08:38 AM - edited 08-16-2017 08:39 AM

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;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aminkarimid

08-15-2017 10:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

08-15-2017 12:24 PM

Would you mind telling me how can I calculate the denaminator?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aminkarimid

08-15-2017 01:26 PM

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

SAS - moving min/max using arrays

SAS Time Series Data - Fill time series with missing time points and calculate moving average

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

08-15-2017 02:50 PM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aminkarimid

08-15-2017 03:27 PM - edited 08-15-2017 03:29 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

08-15-2017 05:24 PM

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.

Nevertheless, thanks for your help.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aminkarimid

08-15-2017 05:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to aminkarimid

08-16-2017 08:38 AM - edited 08-16-2017 08:39 AM

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;
```