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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.