Calculation and Formulation in SAS

[aminkarimid]

 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.

[Reeza]

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

[novinosrin · Accepted solution]

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

[Ksharp]

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;

[aminkarimid]

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.

[Reeza]

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.

[aminkarimid]

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.

[Reeza]

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. 

[aminkarimid]

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.

[Reeza]

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