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.
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;
Can you post this as an example directly into your post? There are restrictions on using zip files in many organizations.
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.
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.
Probably more than we need. What answer do you expect from those 1,000 observations?
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.
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.
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;
@Reeza, I am so grateful for your kindness.
Thank you for your prompt reply.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.