topic Re: FORMULATION IN SAS; in SAS Programming

FORMULATION IN SAS;

aminkarimid — Sun, 14 May 2017 20:52:03 GMT

Hello experts.

Please help me how can I run this formula;

Thanks for your attention.

Re: FORMULATION IN SAS;

Reeza — Sun, 14 May 2017 21:49:24 GMT

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.

Re: FORMULATION IN SAS;

Kurt_Bremser — Mon, 15 May 2017 06:49:39 GMT

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.

Re: FORMULATION IN SAS;

Shmuel — Mon, 15 May 2017 10:26:42 GMT

Can you describe your data?

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.

Please correct my assumptions and add a sample of your data.

Re: FORMULATION IN SAS;

aminkarimid — Mon, 15 May 2017 11:38:14 GMT

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. .

Re: FORMULATION IN SAS;

Kurt_Bremser — Mon, 15 May 2017 12:24:38 GMT

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-data-AKA-generate/ta-p/258712

Please help the ones that are supposed to help you.

Re: FORMULATION IN SAS;

Shmuel — Mon, 15 May 2017 12:51:46 GMT

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.

Re: FORMULATION IN SAS;

aminkarimid — Wed, 17 May 2017 11:03:13 GMT

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

Thanks for your attention.

Re: FORMULATION IN SAS;

Shmuel — Wed, 17 May 2017 11:07:46 GMT

All you need is to preceed the datastep with sorting:

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

Re: FORMULATION IN SAS;

aminkarimid — Wed, 17 May 2017 15:04:28 GMT

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

Re: FORMULATION IN SAS;

aminkarimid — Wed, 17 May 2017 13:00:32 GMT

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