Calculation and Formulation in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

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.

 

datenametimevolumeIntradayVolumeDailyVolume
3/24/2008abc10:00100..
3/24/2008abc10:00120..
3/24/2008abc10:00100320.
3/24/2008abc11:00200..
3/24/2008abc11:00250450.
3/24/2008abc12:30110..
3/24/2008abc12:301202301000
3/24/2008xyz9:30250..
3/24/2008xyz9:30240..
3/24/2008xyz9:30170660.
3/24/2008xyz10:00180..
3/24/2008xyz10:00140..
3/24/2008xyz10:00110430.
3/24/2008xyz12:0090901180
3/25/2008abc9:30110..
3/25/2008abc9:30210..
3/25/2008abc9:30100420.
3/25/2008abc10:00100100.
3/25/2008abc11:00210..
3/25/2008abc11:00220..
3/25/2008abc11:002609:001210
3/25/2008xyz9:00300300.
3/25/2008xyz9:30250..
3/25/2008xyz9:30170..
3/25/2008xyz9:30170590.
3/25/2008xyz11:302002001090

 

 

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:

 

datenametimevolumeIntradayVolumeDailyVolumeAdjustedVolume
3/24/2008abc10:00100...
3/24/2008abc10:00120...
3/24/2008abc10:00100320..
3/24/2008abc11:00200...
3/24/2008abc11:00250450..
3/24/2008abc12:30110...
3/24/2008abc12:301202301000.
3/24/2008xyz9:30250...
3/24/2008xyz9:30240...
3/24/2008xyz9:30170660..
3/24/2008xyz10:00180...
3/24/2008xyz10:00140...
3/24/2008xyz10:00110430..
3/24/2008xyz12:0090901180.
3/25/2008abc9:30110...
3/25/2008abc9:30210...
3/25/2008abc9:30100420.0.42
3/25/2008abc10:00100100.0.1
3/25/2008abc11:00210...
3/25/2008abc11:00220...
3/25/2008abc11:0026069012100.69
3/25/2008xyz9:00300300.0.254237288
3/25/2008xyz9:30250...
3/25/2008xyz9:30170...
3/25/2008xyz9:30170590.0.5
3/25/2008xyz11:3020020010900.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
Frequent Contributor
Posts: 107

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;

                           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

View solution in original post


All Replies
Grand Advisor
Posts: 17,342

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

 

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

Solution
‎05-20-2017 01:31 PM
Frequent Contributor
Posts: 107

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;

                           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

Grand Advisor
Posts: 9,578

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 *,IntradayVolume/
(select avg(DailyVolume) from have where name=a.name and date=a.date-1) 
as AdjustedVolume
 from have as a;
quit;
Contributor
Posts: 71

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.
Please help me.
Grand Advisor
Posts: 17,342

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.

 

 

Contributor
Posts: 71

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.
Grand Advisor
Posts: 17,342

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. 

 

Contributor
Posts: 71

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.

Grand Advisor
Posts: 17,342

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 170 views
  • 4 likes
  • 4 in conversation