## Calculation and Formulation in SAS

Solved
Regular Contributor
Posts: 166

# Calculation and Formulation in SAS

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.

Accepted Solutions
Solution
‎05-20-2017 01:31 PM
PROC Star
Posts: 1,836

## Re: Calculation and Formulation in SAS

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;

end;

if last.date then

temp=dailyvolume;

output;

end;

end;

drop temp n;

run;

proc sort date=want;

by date name;

run;

Regards,

Naveen Srinivasan

All Replies
Super User
Posts: 23,775

## Re: Calculation and Formulation in SAS

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

Solution
‎05-20-2017 01:31 PM
PROC Star
Posts: 1,836

## Re: Calculation and Formulation in SAS

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;

end;

if last.date then

temp=dailyvolume;

output;

end;

end;

drop temp n;

run;

proc sort date=want;

by date name;

run;

Regards,

Naveen Srinivasan

Super User
Posts: 10,787

## Re: Calculation and Formulation in SAS

``````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 avg(DailyVolume) from have where name=a.name and date=a.date-1)
from have as a;
quit;``````
Regular Contributor
Posts: 166

## Re: Calculation and Formulation in SAS

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.
Super User
Posts: 23,775

## Re: Calculation and Formulation in SAS

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.

Regular Contributor
Posts: 166

## Re: Calculation and Formulation in SAS

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.
Super User
Posts: 23,775

## Re: Calculation and Formulation in SAS

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

Regular Contributor
Posts: 166

## Re: Calculation and Formulation in SAS

[ Edited ]

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.

Super User
Posts: 23,775

## Re: Calculation and Formulation in SAS

``new_date = input(current_date, yymmdd10.);``

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

ie

20170131 -> YYMMDD8

☑ This topic is solved.