DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

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:

Formula02.PNG

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
yesterday
Frequent Contributor
Posts: 107

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

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;

 

REF.

View solution in original post


All Replies
Super User
Posts: 17,750

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: 10,466

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.

Frequent Contributor
Posts: 107

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

Hello @ballardw

Sorry about my mistake.

Here is another sample.

I hope it could help you.

Attachment
Super User
Super User
Posts: 6,497

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.

Frequent Contributor
Posts: 107

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
Super User
Posts: 6,497

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?

Frequent Contributor
Posts: 107

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: 17,750

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
Super User
Posts: 6,497

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.

Frequent Contributor
Posts: 107

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
yesterday
Frequent Contributor
Posts: 107

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

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;

 

REF.

Super User
Posts: 17,750

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

And my recommendation was the second answer you received here:
https://stackoverflow.com/questions/45699911/how-can-i-calculate-the-division-using-sas
Frequent Contributor
Posts: 107

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.

Thank you for your prompt reply.

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 185 views
  • 0 likes
  • 4 in conversation