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.
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;
end;
drop temp n;
run;
proc sort date=want;
by date name;
run;
Regards,
Naveen Srinivasan
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
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;
end;
drop temp n;
run;
proc sort date=want;
by date name;
run;
Regards,
Naveen Srinivasan
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;
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.
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.