## FORMULATION IN SAS;

Solved
Regular Contributor
Posts: 166

# FORMULATION IN SAS;

Hello experts.

Accepted Solutions
Solution
‎05-18-2017 04:31 AM
Posts: 1,837

## Re: FORMULATION IN SAS;

[ Edited ]

Assuming Volum_Sum is per TRD_STCK_CD in a TRD_STCK_CD  (i.e. - per date)

you shall achieve it by next code:

``````data sample_data;
infile datalines;
input TRD_EVENT_DT mmddyy10.
TRD_EVENT_TM time8.
TRD_STCK_CD  \$5.
TRD_PR       4.
TRD_TUROVR   5.
TRD_EVENT_ROUFOR timr5.
VOLUME       8.
;

datalines;
03/24/2008 12:28:01 ALBZ1 1537 10000 12:30 15370000
03/24/2008 13:13:44 ALBZ1 1567 10	 13:00 15670
03/24/2008 12:20:38 AZAB1 683  10000 12:30 6830000
03/24/2008 13:13:44 AZAB1 695  10	 13:00 6950
03/24/2008 09:14:57 BALI1 850  9260	 09:00 7871000
03/24/2008 09:15:06 BALI1 850  2000	 09:30 1700000
03/24/2008 09:15:14 BALI1 850  10000 09:30 8500000
03/24/2008 09:15:24 BALI1 850  6000	 09:30 5100000
03/24/2008 09:29:27 BALI1 850  10000 09:30 8500000
03/24/2008 12:28:00 BALI1 850  10000 12:30 8500000
03/24/2008 12:28:07 BALI1 850  10000 12:30 8500000
03/24/2008 13:13:44 BALI1 865  10	 13:00 8650
; run;

data want;
set sample_data;
by TRD_STCK_CD  TRD_STCK_CD  ;
retain vol_sum;
drop_vol_sum;
if first.TRD_STCK_CD    then vol_sum=0;
vol_sum = sum(of vol_sum, volume);
if last.TRD_STCK_CD then do;
Volume_sum = vol_sum;
output;
end;
run;``````

Please, next time attach the sample dataset as shown in first step.

All Replies
Super User
Posts: 23,776

## Re: FORMULATION IN SAS;

Can you show that with a data sample instead and expected output?

This is based on the assumption you understand the formula and are looking for a technical answer on how to implement it given data. Since it partly depends on your data structure how it's achieved you should show your data.

Super User
Posts: 10,280

## Re: FORMULATION IN SAS;

The proper way to post SAS datasets as data step and a macro for conversion is found here. To apply your formula to data, knowledge of the data is essential.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Posts: 1,837

## Re: FORMULATION IN SAS;

I guess:

- VOLUME is the amount of some material  in a given time

- The Volume changes along time and measured at every half an hour

Is your data given as an array of the measured Volume ?

or as a list of observations? The SAS code will differ in those two situations,

though it is possible to convert each to the other.

Regular Contributor
Posts: 166

## Re: FORMULATION IN SAS;

Hello;

This is a sample of my data:

 TRD_EVENT_DT TRD_EVENT_TM TRD_EVENT_MS TRD_STCK_ID TRD_STCK_CD TRD_INSTR_NAME TRD_STCK_GRP_ID_CD TRD_PR TRD_TUROVR TRD_TRA_NR 3/24/2008 12:19:35 IRB5IKCO8751 IKCQ1 187191011 N2 1 100 122197901 3/24/2008 12:28:01 IRO1ALBZ0001 ALBZ1 والبر1 N1 1537 10000 122198037 3/24/2008 13:13:44 IRO1ALBZ0001 ALBZ1 والبر1 N1 1567 10 9999198173 3/24/2008 12:20:38 IRO1AZAB0001 AZAB1 فاذر1 N2 683 10000 122197911 3/24/2008 13:13:44 IRO1AZAB0001 AZAB1 فاذر1 N2 695 10 9999198167 3/24/2008 09:14:57 IRO1BALI0001 BALI1 وبوعلي1 N2 850 9260 122197242 3/24/2008 09:15:06 IRO1BALI0001 BALI1 وبوعلي1 N2 850 2000 122197245 3/24/2008 09:15:14 IRO1BALI0001 BALI1 وبوعلي1 N2 850 10000 122197247 3/24/2008 09:15:24 IRO1BALI0001 BALI1 وبوعلي1 N2 850 6000 122197249 3/24/2008 09:29:27 IRO1BALI0001 BALI1 وبوعلي1 N2 850 10000 122197296 3/24/2008 12:28:00 IRO1BALI0001 BALI1 وبوعلي1 N2 850 10000 122198036 3/24/2008 12:28:07 IRO1BALI0001 BALI1 وبوعلي1 N2 850 10000 122198041 3/24/2008 13:13:44 IRO1BALI0001 BALI1 وبوعلي1 N2 865 10 9999198133

Explanation of headers of some columns:

[TRD_EVENT_DT] Month / Day / Year
,[TRD_EVENT_TM] Time: hh:mm:ss
,[TRD_EVENT_MS] Millisecond
,[TRD_STCK_ID]
,[TRD_STCK_CD] Stock ID
,[TRD_INSTR_NAME]
,[TRD_STCK_GRP_ID_CD]
,[TRD_PR] Stock Price
,[TRD_TUROVR] Turnover
,[TRD_TRA_NR]

I know how can I round time; my code is below:

```*Step1: Rounding Time;
DATA Sampledata87_New;
SET Sampledata87;
TRD_EVENT_TIME = INPUT(TRD_EVENT_TM,time16.);
TRD_EVENT_ROUNDED = ROUND(TRD_EVENT_TIME,'00:30't);
TRD_EVENT_ROUFOR = PUT(TRD_EVENT_ROUNDED,hhmm.);```

And volume is:

```*Step2: Calculation Of Volume;
VOLUME = TRD_PR*TRD_TUROVR;```

Now, I have this data;

SAS Output

Obs TRD_EVENT_DT TRD_EVENT_TM TRD_EVENT_MS TRD_STCK_ID TRD_STCK_CD TRD_INSTR_NAME TRD_STCK_GRP_ID_CD TRD_PR TRD_TUROVR TRD_TRA_NR TRD_EVENT_TIME TRD_EVENT_ROUNDED TRD_EVENT_ROUFOR VOLUME 1 2 3 4 5 6 7 8 9 10 11 12 13
 03/24/2008 12:19:35 IRB5IKCO8751 IKCQ1 187191011 N2 1 100 122197901 44375 45000 12:30 100 03/24/2008 12:28:01 IRO1ALBZ0001 ALBZ1 H'D(11 N1 1537 10000 122198037 44881 45000 12:30 15370000 03/24/2008 13:13:44 IRO1ALBZ0001 ALBZ1 H'D(11 N1 1567 10 9999198173 47624 46800 13:00 15670 03/24/2008 12:20:38 IRO1AZAB0001 AZAB1 A'011 N2 683 10000 122197911 44438 45000 12:30 6830000 03/24/2008 13:13:44 IRO1AZAB0001 AZAB1 A'011 N2 695 10 9999198167 47624 46800 13:00 6950 03/24/2008 09:14:57 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 9260 122197242 33297 32400 9:00 7871000 03/24/2008 09:15:06 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 2000 122197245 33306 34200 9:30 1700000 03/24/2008 09:15:14 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 10000 122197247 33314 34200 9:30 8500000 03/24/2008 09:15:24 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 6000 122197249 33324 34200 9:30 5100000 03/24/2008 09:29:27 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 10000 122197296 34167 34200 9:30 8500000 03/24/2008 12:28:00 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 10000 122198036 44880 45000 12:30 8500000 03/24/2008 12:28:07 IRO1BALI0001 BALI1 H(H9DJ1 N2 850 10000 122198041 44887 45000 12:30 8500000 03/24/2008 13:13:44 IRO1BALI0001 BALI1 H(H9DJ1 N2 865 10 9999198133 47624 46800 13:00 8650

But for volume for one day and one stock, I need sum all volume data of one day. It must Be distinguished by  TRD_STCK_CD var. .

Super User
Posts: 10,280

## Re: FORMULATION IN SAS;

I hate to repeat myself, but:

A MACRO TO CONVERT DATASETS TO DATASTEPS FOR POSTING CAN BE FOUND HERE:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Posts: 1,837

## Re: FORMULATION IN SAS;

You gave more variable names then data displayed.

Anyhow, it seems to me that only next variables are used in your formula:

- TRD_EVENT_DT                                 - The date, as you want sum volume per day

- TRD_STCK_ID  or TRD_STCK_CD     - One of then is enough as they relate 1:1

- TRD_PR                                              - The price

- TRD_TUROVR                                    - The turnover

Am I right, your code should look like:

data temp;

set have;

by TRD_EVENT_DT  TRD_STCK_CD  ;

retain volume;

if first.TRD_STCK_CD    then volume=0;

volume = sum(of volume, TRD_PR *  TRD_TUROVR);

if last.TRD_STCK_CD then output;

run;

Then you may need second step to compute the division.

I don't see the whole picture. Probably there is a procedur to it in one step.

Regular Contributor
Posts: 166

## Re: FORMULATION IN SAS;

Thank you;

But There is still a problem: I have shown it below:

```432  data sampledata_new;
433    set sampledata87_New;
434          by TRD_EVENT_DT TRD_STCK_CD;
435               retain volume;
436              if first.TRD_STCK_CD    then volume=0;
437              volume = sum(of volume, TRD_PR *  TRD_TUROVR);
438              if last.TRD_STCK_CD then output;
439  run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
437:37   437:47
ERROR: BY variables are not properly sorted on data set WORK.SAMPLEDATA87_NEW.
TRD_EVENT_DT=03/24/2008 TRD_EVENT_TM=12:19:35 TRD_EVENT_MS=  TRD_STCK_ID=IRB5IKCO8751
TRD_STCK_CD=IKCQ1 TRD_INSTR_NAME=187191011 TRD_STCK_GRP_ID_CD=N2 TRD_PR=1 TRD_TUROVR=100
TRD_TRA_NR=122197901 TRD_EVENT_TIME=44375 TRD_EVENT_ROUNDED=45000 TRD_EVENT_ROUFOR=12:30 VOLUME=100
FIRST.TRD_EVENT_DT=1 LAST.TRD_EVENT_DT=1 FIRST.TRD_STCK_CD=1 LAST.TRD_STCK_CD=1 _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 2 observations read from the data set WORK.SAMPLEDATA87_NEW.
WARNING: The data set WORK.SAMPLEDATA_NEW may be incomplete.  When this step was stopped there were 0
observations and 14 variables.
WARNING: Data set WORK.SAMPLEDATA_NEW was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time           0.04 seconds
cpu time            0.03 seconds

```

Posts: 1,837

## Re: FORMULATION IN SAS;

All you need is to preceed the datastep with sorting:

proc sort data=sampledata87_New ; by TRD_EVENT_DT TRD_STCK_CD;  run;

Regular Contributor
Posts: 166

## Re: FORMULATION IN SAS;

Thank you so much Shmuel, But the final results is wrong. I have described it below, in next post.
Regular Contributor
Posts: 166

## Re: FORMULATION IN SAS;

Hello everybody;

there is a sample of my data:

 TRD_EVENT_DT TRD_EVENT_TM TRD_STCK_CD TRD_PR TRD_TUROVR TRD_EVENT_ROUFOR VOLUME 3/24/2008 12:28:01 ALBZ1 1537 10000 12:30 15370000 3/24/2008 13:13:44 ALBZ1 1567 10 13:00 15670 3/24/2008 12:20:38 AZAB1 683 10000 12:30 6830000 3/24/2008 13:13:44 AZAB1 695 10 13:00 6950 3/24/2008 09:14:57 BALI1 850 9260 9:00 7871000 3/24/2008 09:15:06 BALI1 850 2000 9:30 1700000 3/24/2008 09:15:14 BALI1 850 10000 9:30 8500000 3/24/2008 09:15:24 BALI1 850 6000 9:30 5100000 3/24/2008 09:29:27 BALI1 850 10000 9:30 8500000 3/24/2008 12:28:00 BALI1 850 10000 12:30 8500000 3/24/2008 12:28:07 BALI1 850 10000 12:30 8500000 3/24/2008 13:13:44 BALI1 865 10 13:00 8650

I have deleted some col. for simplicity. In next step, I want a table such as below:

 TRD_EVENT_DT TRD_EVENT_TM TRD_STCK_CD TRD_PR TRD_TUROVR TRD_EVENT_ROUFOR VOLUME Volume_Sum 3/24/2008 12:28:01 ALBZ1 1537 10000 12:30 15370000 3/24/2008 13:13:44 ALBZ1 1567 10 13:00 15670 15385670 3/24/2008 12:20:38 AZAB1 683 10000 12:30 6830000 3/24/2008 13:13:44 AZAB1 695 10 13:00 6950 6836950 3/24/2008 09:14:57 BALI1 850 9260 9:00 7871000 3/24/2008 09:15:06 BALI1 850 2000 9:30 1700000 3/24/2008 09:15:14 BALI1 850 10000 9:30 8500000 3/24/2008 09:15:24 BALI1 850 6000 9:30 5100000 3/24/2008 09:29:27 BALI1 850 10000 9:30 8500000 3/24/2008 12:28:00 BALI1 850 10000 12:30 8500000 3/24/2008 12:28:07 BALI1 850 10000 12:30 8500000 3/24/2008 13:13:44 BALI1 865 10 13:00 8650 48679650

Please pay attention to last col. It has been generated by summing volumes that have same TRD_STCK_CD var. .So each TRD_STCK_CD obs. has just one Volume_Sum data.

Thank you.

Regular Contributor
Posts: 166

## Re: FORMULATION IN SAS;

[ Edited ]

Thank you Shmuel; But this code gives the last volume number ,and then It is multiplied by 2.

Solution
‎05-18-2017 04:31 AM
Posts: 1,837

## Re: FORMULATION IN SAS;

[ Edited ]

Assuming Volum_Sum is per TRD_STCK_CD in a TRD_STCK_CD  (i.e. - per date)

you shall achieve it by next code:

``````data sample_data;
infile datalines;
input TRD_EVENT_DT mmddyy10.
TRD_EVENT_TM time8.
TRD_STCK_CD  \$5.
TRD_PR       4.
TRD_TUROVR   5.
TRD_EVENT_ROUFOR timr5.
VOLUME       8.
;

datalines;
03/24/2008 12:28:01 ALBZ1 1537 10000 12:30 15370000
03/24/2008 13:13:44 ALBZ1 1567 10	 13:00 15670
03/24/2008 12:20:38 AZAB1 683  10000 12:30 6830000
03/24/2008 13:13:44 AZAB1 695  10	 13:00 6950
03/24/2008 09:14:57 BALI1 850  9260	 09:00 7871000
03/24/2008 09:15:06 BALI1 850  2000	 09:30 1700000
03/24/2008 09:15:14 BALI1 850  10000 09:30 8500000
03/24/2008 09:15:24 BALI1 850  6000	 09:30 5100000
03/24/2008 09:29:27 BALI1 850  10000 09:30 8500000
03/24/2008 12:28:00 BALI1 850  10000 12:30 8500000
03/24/2008 12:28:07 BALI1 850  10000 12:30 8500000
03/24/2008 13:13:44 BALI1 865  10	 13:00 8650
; run;

data want;
set sample_data;
by TRD_STCK_CD  TRD_STCK_CD  ;
retain vol_sum;
drop_vol_sum;
if first.TRD_STCK_CD    then vol_sum=0;
vol_sum = sum(of vol_sum, volume);
if last.TRD_STCK_CD then do;
Volume_sum = vol_sum;
output;
end;
run;``````

Please, next time attach the sample dataset as shown in first step.

Regular Contributor
Posts: 166

## Re: FORMULATION IN SAS;

[ Edited ]

Here is another solution:

http://stackoverflow.com/a/44034569/7996949

```/*Sort by TRD_STCK_CD and temporal variables.*/
proc sort data=have out=have_sorted;
by  TRD_STCK_CD
TRD_EVENT_DT
TRD_EVENT_TM;
run;

/*Sum VOLUME until the last of each TRD_STCK_CD is reached.*/
data want;
set have_sorted;
by  TRD_STCK_CD
TRD_EVENT_DT
TRD_EVENT_TM;
retain tmp_volume_sum;
tmp_volume_sum + VOLUME;
if last.TRD_STCK_CD then do;
Volume_Sum = tmp_volume_sum;
call missing(tmp_volume_sum);
end;
drop tmp_:;
run;```

Thank you.

Regular Contributor
Posts: 166