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
- /
- Base SAS Programming
- /
- How to adjust a variable via dividing it by the su...

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

07-31-2017 11:48 AM - edited 08-01-2017 11:51 AM

Hello everybody,

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

And I use codes which are shown below:

*Division for calculating adjusted volume; proc sort data=sampledata_sumvolso02 out=sampledata_sumvolso02; by TRD_STCK_CD TRD_EVENT_DT; run; *Adjusted intraday volume for days of a week; data sampledata_adjvol02; 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; 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_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 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,, ;;;;

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?

Thanks in advance.

Accepted Solutions

Solution

08-16-2017
09:11 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 09:11 AM

Here is a solution:

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

07-31-2017 12:30 PM

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

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

Posted in reply to aminkarimid

07-31-2017 01:06 PM

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.

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

Posted in reply to ballardw

07-31-2017 02:02 PM

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

Posted in reply to aminkarimid

07-31-2017 02:39 PM - edited 07-31-2017 02:42 PM

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.

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

07-31-2017 03:48 PM

Hello Tom.

I pasted 1000 obs. directly into your post.

I pasted 1000 obs. directly into your post.

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

Posted in reply to aminkarimid

07-31-2017 03:51 PM

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

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

07-31-2017 04:06 PM

would you please tell me if my question is ambiguous?

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

Posted in reply to aminkarimid

07-31-2017 04:23 PM

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.

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

Posted in reply to aminkarimid

07-31-2017 01:08 PM - edited 07-31-2017 01:10 PM

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.

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

07-31-2017 02:09 PM

Hello,

The day variable is TRD_EVENT_DT.

The CountedVOLUME is TRD_PR *TRD_TUROVR.

The TRD_STICK_CD is a name variable.

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

- 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 09:11 AM

Here is a solution:

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

- 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 10:45 AM

And my recommendation was the second answer you received here:

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

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

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

Posted in reply to Reeza

08-16-2017 11:40 AM - edited 08-16-2017 11:59 AM

@Reeza, I am so grateful for your kindness.

Thank you for your prompt reply.