BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aminkarimid
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
aminkarimid
Lapis Lazuli | Level 10

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

13 REPLIES 13
Reeza
Super User

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

ballardw
Super User

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.

aminkarimid
Lapis Lazuli | Level 10

Hello @ballardw

Sorry about my mistake.

Here is another sample.

I hope it could help you.

Tom
Super User Tom
Super User

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.

aminkarimid
Lapis Lazuli | Level 10
Hello Tom.
I pasted 1000 obs. directly into your post.
Tom
Super User Tom
Super User

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

aminkarimid
Lapis Lazuli | Level 10
would you please tell me if my question is ambiguous?
Reeza
Super User

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. 

Tom
Super User Tom
Super User

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.

aminkarimid
Lapis Lazuli | Level 10
Hello,
The day variable is TRD_EVENT_DT.
The CountedVOLUME is TRD_PR *TRD_TUROVR.
The TRD_STICK_CD is a name variable.
aminkarimid
Lapis Lazuli | Level 10

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.

aminkarimid
Lapis Lazuli | Level 10

@Reeza, I am so grateful for your kindness.

Thank you for your prompt reply.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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