topic Re: Calculation and Formulation in SAS in SAS Programming

Calculation and Formulation in SAS

aminkarimid — Fri, 19 May 2017 12:15:29 GMT

Hello everybody; I have this table that has been shown below:

IntradayVolume (The volume of trade of a specific equity in a specific time of the day) = sum (volume) based on date, name and time;

DailyVolume (The volume of trade of a specific equity in a specific day) = sum (IntradayVolume) based on date and name.

date	name	time	volume	IntradayVolume	DailyVolume
3/24/2008	abc	10:00	100	.	.
3/24/2008	abc	10:00	120	.	.
3/24/2008	abc	10:00	100	320	.
3/24/2008	abc	11:00	200	.	.
3/24/2008	abc	11:00	250	450	.
3/24/2008	abc	12:30	110	.	.
3/24/2008	abc	12:30	120	230	1000
3/24/2008	xyz	9:30	250	.	.
3/24/2008	xyz	9:30	240	.	.
3/24/2008	xyz	9:30	170	660	.
3/24/2008	xyz	10:00	180	.	.
3/24/2008	xyz	10:00	140	.	.
3/24/2008	xyz	10:00	110	430	.
3/24/2008	xyz	12:00	90	90	1180
3/25/2008	abc	9:30	110	.	.
3/25/2008	abc	9:30	210	.	.
3/25/2008	abc	9:30	100	420	.
3/25/2008	abc	10:00	100	100	.
3/25/2008	abc	11:00	210	.	.
3/25/2008	abc	11:00	220	.	.
3/25/2008	abc	11:00	260	9:00	1210
3/25/2008	xyz	9:00	300	300	.
3/25/2008	xyz	9:30	250	.	.
3/25/2008	xyz	9:30	170	.	.
3/25/2008	xyz	9:30	170	590	.
3/25/2008	xyz	11:30	200	200	1090

Now, I want to calculate this formula:

Adjusted volume = IntradayVolume (based on a specific time of today) / DailyVolume (based on Previous day)

So I will have this table that has been shown below:

date	name	time	volume	IntradayVolume	DailyVolume	AdjustedVolume
3/24/2008	abc	10:00	100	.	.	.
3/24/2008	abc	10:00	120	.	.	.
3/24/2008	abc	10:00	100	320	.	.
3/24/2008	abc	11:00	200	.	.	.
3/24/2008	abc	11:00	250	450	.	.
3/24/2008	abc	12:30	110	.	.	.
3/24/2008	abc	12:30	120	230	1000	.
3/24/2008	xyz	9:30	250	.	.	.
3/24/2008	xyz	9:30	240	.	.	.
3/24/2008	xyz	9:30	170	660	.	.
3/24/2008	xyz	10:00	180	.	.	.
3/24/2008	xyz	10:00	140	.	.	.
3/24/2008	xyz	10:00	110	430	.	.
3/24/2008	xyz	12:00	90	90	1180	.
3/25/2008	abc	9:30	110	.	.	.
3/25/2008	abc	9:30	210	.	.	.
3/25/2008	abc	9:30	100	420	.	0.42
3/25/2008	abc	10:00	100	100	.	0.1
3/25/2008	abc	11:00	210	.	.	.
3/25/2008	abc	11:00	220	.	.	.
3/25/2008	abc	11:00	260	690	1210	0.69
3/25/2008	xyz	9:00	300	300	.	0.254237288
3/25/2008	xyz	9:30	250	.	.	.
3/25/2008	xyz	9:30	170	.	.	.
3/25/2008	xyz	9:30	170	590	.	0.5
3/25/2008	xyz	11:30	200	200	1090	0.169491525

I have calculated IntradayVolume & DailyVolume variables in SAS.

How can I calculate 'Adjusted volume' varaible using SAS?

Thanks.

Re: Calculation and Formulation in SAS

Reeza — Fri, 19 May 2017 12:28:41 GMT

I think rather than try and do this in a DATA STEP you should use a merge instead.

See the example here on how to merge a summary stat in

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset

Re: Calculation and Formulation in SAS

novinosrin — Fri, 19 May 2017 12:51:55 GMT

data have;

input date :mmddyy10. name $ time $ volume IntradayVolume DailyVolume $;

format date date9.;

datalines;

3/24/2008 abc 10:00 100 . .

3/24/2008 abc 10:00 120 . .

3/24/2008 abc 10:00 100 320 .

3/24/2008 abc 11:00 200 . .

3/24/2008 abc 11:00 250 450 .

3/24/2008 abc 12:30 110 . .

3/24/2008 abc 12:30 120 230 1000

3/24/2008 xyz 9:30 250 . .

3/24/2008 xyz 9:30 240 . .

3/24/2008 xyz 9:30 170 660 .

3/24/2008 xyz 10:00 180 . .

3/24/2008 xyz 10:00 140 . .

3/24/2008 xyz 10:00 110 430 .

3/24/2008 xyz 12:00 90 90 1180

3/25/2008 abc 9:30 110 . .

3/25/2008 abc 9:30 210 . .

3/25/2008 abc 9:30 100 420 .

3/25/2008 abc 10:00 100 100 .

3/25/2008 abc 11:00 210 . .

3/25/2008 abc 11:00 220 . .

3/25/2008 abc 11:00 260 690 1210

3/25/2008 xyz 9:00 300 300 .

3/25/2008 xyz 9:30 250 . .

3/25/2008 xyz 9:30 170 . .

3/25/2008 xyz 9:30 170 590 .

3/25/2008 xyz 11:30 200 200 1090

;

proc sort data=have out=have1;

by name date;

run;

data want;

do until(last.name);

do until(last.date);

set have1;

by name date;

if first.name then

n=0;

if first.date then

n+1;

if n>1 then

do;

if not missing(IntradayVolume) then

adjusted_volume=divide(IntradayVolume,temp);

else call missing(adjusted_volume);

end;

if last.date then

temp=dailyvolume;

output;

end;

drop temp n;

run;

proc sort date=want;

by date name;

run;

Regards,

Naveen Srinivasan

Re: Calculation and Formulation in SAS

Ksharp — Fri, 19 May 2017 13:03:05 GMT

data have;
input date :mmddyy10.  name $ time $ volume IntradayVolume DailyVolume ;
format date date9.;
datalines;
3/24/2008 abc 10:00 100 . .
3/24/2008 abc 10:00 120 . .
3/24/2008 abc 10:00 100 320 .
3/24/2008 abc 11:00 200 . .
3/24/2008 abc 11:00 250 450 .
3/24/2008 abc 12:30 110 . .
3/24/2008 abc 12:30 120 230 1000
3/24/2008 xyz 9:30 250 . .
3/24/2008 xyz 9:30 240 . .
3/24/2008 xyz 9:30 170 660 .
3/24/2008 xyz 10:00 180 . .
3/24/2008 xyz 10:00 140 . .
3/24/2008 xyz 10:00 110 430 .
3/24/2008 xyz 12:00 90 90 1180
3/25/2008 abc 9:30 110 . .
3/25/2008 abc 9:30 210 . .
3/25/2008 abc 9:30 100 420 .
3/25/2008 abc 10:00 100 100 .
3/25/2008 abc 11:00 210 . .
3/25/2008 abc 11:00 220 . .
3/25/2008 abc 11:00 260 690 1210
3/25/2008 xyz 9:00 300 300 .
3/25/2008 xyz 9:30 250 . .
3/25/2008 xyz 9:30 170 . .
3/25/2008 xyz 9:30 170 590 .
3/25/2008 xyz 11:30 200 200 1090
;
proc sql;
create table want as
select *,IntradayVolume/
(select avg(DailyVolume) from have where name=a.name and date=a.date-1) 
as AdjustedVolume
 from have as a;
quit;

Re: Calculation and Formulation in SAS

aminkarimid — Sun, 21 May 2017 17:15:55 GMT

Thank you Ksharp, But it shows errors:
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Expression using equals (=) has components that are of different data types.
Please help me.

Re: Calculation and Formulation in SAS

Reeza — Sun, 21 May 2017 18:36:42 GMT

Make sure your variables have the correct type. You can compare them to the ones in @Ksharp example.

You haven't posted the full errors so we can't even tell you what variables you're going to need to fix - the errors will have that information.

Re: Calculation and Formulation in SAS

aminkarimid — Sun, 21 May 2017 21:47:19 GMT

I have used exactly Ksharp's codes. I know that the code run very well for the numbers that have been shown in the previous post. But I want to use it for calculating all numbers.
I know that error is about the variable formats. But I don't know how can I change it and which format is suitable.

Re: Calculation and Formulation in SAS

Reeza — Sun, 21 May 2017 21:54:33 GMT

ERROR: Expression using subtraction (-) requires numeric types.

So which variables are being subtracted? -> from code it looks like the DATE variable is the only subtraction.

And the error states it needs to be numeric. Use INPUT to convert it to a numeric variable.

You would need to show the actual type and how it looks before we could say how to convert it.

@aminkarimid wrote:

I know that error is about the variable formats. But I don't know how can I change it and which format is suitable.

Don't mix formats and types. Formats control how a variable appears but the there are only two underlying types, numeric and character. You have type mismatches here.

Re: Calculation and Formulation in SAS

aminkarimid — Sun, 21 May 2017 22:08:47 GMT

Hello;

For current date:
Length=10
Format=$10.
Informat=$10.

The output of Ksharp's code has the information below for date:
Length=8
Format=DATE9.
Informat=9.

Re: Calculation and Formulation in SAS

Reeza — Sun, 21 May 2017 22:58:57 GMT

new_date = input(current_date, yymmdd10.);

The INFORMAT, second parameter to the INPUT() function, should be what the date variable currently looks like.

20170131 -> YYMMDD8