Help with SAS code

[malakaext]

Hi,

I'm new to sas and having a hard time finding the monthly variance in the daily data set(for column return) I have attached here ( i have deleted some part of the data to make the file smaller).

I want to plot monthly variance of this daily data from 2000 to 2010 so that I can see if there's a pattern.

The column headings are: date : open, high, low, close, volume, adjclose, return

Please be kind enough to help me with this coding.

thanks.

PS. Im using SAS 9.0

Malaka

[MikeZdeb · Accepted solution]

hi ... try this (it plots all the variables) ... one example plot is attached ...

* your data;

data test;

infile 'z:\ftsehelp.txt' dsd dlm='09'x;

input date :mmddyy. OPEN HIGH LOW CLOSE VOLUME ADJCLOSE RETURN;

format date mmddyy10.;

run;

* calculate monthly variance for all variables;

proc summary data=test nway;

class date;

var open high low close volume adjclose return;

output out=month_var (drop=_:) var= ;

format date monyy.;

run;

* rearrange data for by-group processing in GPLOT;

proc transpose data=month_var out=tmonth_var (rename=(col1=var));

by date;

format date monyy.;

run;

proc sort data=tmonth_var;

by _name_ date;

run;

options nobyline;

* set graphics options;

goptions reset=all ftext='calibri' htext=2 gunit=pct;

* choose a symbol ... use a spline to fit to the monthly data;

symbol f='wingdings' v='6c'x i=smp75 c=blue h=2 w=2;

axis1 minor=(n=11) label=("MONTH-YEAR") offset=(2,2)pct;

axis2 label=none offset=(0,0)pct ;

title1 h=4 'PLOT OF #byval(_name_) BY MONTH AND YEAR' ls=2;

* add white s[ace around plot;

title2 a=90 ls=2;

title3 a=-90 ls=2;

footnote1 ls=1;

* plot all the variables;

proc gplot data=tmonth_var;

plot var*date / haxis=axis1 vaxis=axis2 noframe;

by _name_ ;

run;

quit;

[malakaext]

Wow... that's amazing...

Thank you very much!!!!!!!!!!

[MikeZdeb]

Hi ... thanks, but it's only amazing if you haven't done it before (I've used SAS/Graph a lot).  I guess I gave you a correct answer.

[malakaext]

lol you could be right...I am really new to SAS and it's really interesting

However, due to lack of experience it's really hard for a guy like me to write a code like this so fast.

Anyway, I greatly appreciate your help in such a short time.. I have already marked your answer as the correct one.

Do you know  what code i can use to fit a line graph (joining every point) instead of a spline?

and can u pls explain the following part:

proc sort data=tmonth_var;

by _name_ date;

run;

Where does _name_date come from?

Thanks

[MikeZdeb]

hi ... _NAME_ is produced by PROC TRANSPOSE ...

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000063675.htm

[malakaext]

hi,

can you please tell me how to graph the variance for each year. I tried splitting the date into three components mm, dd, yyyy and then using

proc gplot data=ftse3;

by yyyy;

plot RETURN*yyyy;

run;

quit;

but it didnt work.

Thanks

[MikeZdeb]

hi ... not sure if this is what you want ...

* your data;

data test;

infile 'J:\sas_questions\data\ftsehelp.txt' dsd dlm='09'x;

input date :mmddyy. OPEN HIGH LOW CLOSE VOLUME ADJCLOSE RETURN;

format date mmddyy10.;

run;

* calculate monthly variance for all variables;

proc summary data=test nway;

class date;

var open high low close volume adjclose return;

output out=month_var (drop=_:) var= ;

format date monyy.;

run;

* NEW ............... add a YEAR variable for later by-group processing;

data month_var;

set month_var;

year = year(date);

run;

* rearrange data for by-group processing in GPLOT;

proc transpose data=month_var out=tmonth_var (rename=(col1=var));

by year date;

format date monyy.;

run;

proc sort data=tmonth_var;

by year _name_ date;

run;

options nobyline;

* set graphics options;

goptions reset=all ftext='calibri' htext=2 gunit=pct;

* choose a symbol ... use a spline to fit to the monthly data;

symbol f='wingdings' v='6c'x i=smp75 c=blue h=2 w=2;

axis1 minor=none label=("MONTH") offset=(2,2)pct;

axis2 label=none offset=(0,0)pct ;

* add YEAR to title;

title1 h=4 'PLOT OF #byval(_name_) BY MONTH, #byval(year)    ' ls=2;

* add white s[ace around plot;

title2 a=90 ls=2;

title3 a=-90 ls=2;

footnote1 ls=1;

* plot all the variables ... use two by-variables;

proc gplot data=tmonth_var;

plot var*date / haxis=axis1 vaxis=axis2 noframe;

by year _name_ ;

run;

quit;

[malakaext]

Hi I really apreciate it.

However, I need to plot daily variances for all 12 month in a given year. not just one summary value for each month.

Can u help me with that please.

Thanks

[MikeZdeb]

Hi ... I don't understand what "daily variance" means.  There's only one observation per day.

Do you mean just plot all the daily points for a year on one chart?

[malakaext]

Here's what I would like to plot.

I want x axis to denote the day of the year and y axis to denote the variance per each day. (plot of variance for each year -there are like 250 days with variances for each year)

So I want to draw a graph for each year. since there are 20 years the sas code should generate 20 such graphs.

I did it in excel but it takes a lot of time to separate variances for each year and draw graphs

Thanks

[MikeZdeb]

hi ... I'm still not sure what you mean by "VARIANCES" ... anyways, I took the data you posted in TEST.CSV

it has a date plus these variables ... OPEN HIGH LOW CLOSE VOLUME ADJ RETURN

the following produces one plot per year using the variable OPEN (the x-axis is day of the year while the y-axis is OPEN)

there are two ways to use the SAS code given that you are looking at data one year at a time

for the y-axis, use AXIS2 if you want the y-axis range to vary by year based on the data or use AXIS3 if you want the same axis range used for each year

you would change it in this statement (here, AXIS3 is used) ... plot open*date / haxis=axis1 vaxis=axis3 noframe;

the two attached plots show 2011 data with a y-axis that varies by data values versus one that used a constant (same) range across all the years

is this what you want (I'm still not sure)

* your posted data;

data x (drop=m d y);

infile 'z:\test.csv' dsd firstobs=2;

input m d y OPEN HIGH LOW CLOSE VOLUME ADJ RETURN;

if m;

* create a real date from month, day, and year;

date = mdy(m,d,y);

year = year(date);

format date date9.;

run;

* arrange in order by date;

proc sort data=x;

by date;

run;

* find min/max values for y-axis ... used if same  y-axis range wanted for each year;

proc sql noprint;

select round(min(open)-500,500.) , round(max(open)+500, 500.) into :min, :max from x;

quit;

options nobyline;

* set graphics options;

goptions reset=all ftext='calibri' htext=2 gunit=pct;

* choose a symbol ... use a spline to fit to the monthly data;

symbol f='wingdings' v='6c'x i=smp66 c=blue h=2 w=2;

axis1 minor=none label=("MONTH-YEAR") offset=(2,2)pct;

axis2 label=none;

axis3 label=none order=&min to &max by 500;

title1 h=4 'DAILY VARIABILITY OF OPEN ... #byval(year)  ' ls=2;

* add white space around plot;

title2 a=90 ls=2;

title3 a=-90 ls=2;

footnote1 ls=1;

* plot the values;

proc gplot data=x;

plot open*date / haxis=axis1 vaxis=axis3 noframe;

by year;

format date monyy. open comma.;

run;

quit;

[malakaext]

Hi,

I guess I could write the code that generates the plots I want.

If you run it u'll be able to understand what I was looking for.

HOWEVER, I'd like to print the 20 graphs on the same page.

Could you please give me a code that would generate all 20 graphs on the same page?

data test;

    infile "F:\ftse.csv"  DSD MISSOVER;

      input date :MMDDYY9. open high low close volume adjclose RETURN month box logprice actdiff;

    format date mmddyy10.;

run;

data test1;

set test;

mm = month (date);

year = year (date);

keep mm year return date;

run;

options validvarname=upcase;

goptions ftext='Arial' htext=2 gunit=pct;

symbol1 v=dot i=join;

axis1 label=(angle=90 "Variance");

title h=4 "Yearly Variance";

footnote j=right "Source: FTSE Data-UK";

proc gplot data = test1 ;

by year ;

plot return*date/ overlay;

run;

[malakaext]

Hi,

I guess I could write the code that generates the plots I want.

If you run it u'll be able to understand what I was looking for.

HOWEVER, I'd like to print the 20 graphs on the same page.

Could you please give me a code that would generate all 20 graphs on the same page?

data test;

    infile "F:\ftse.csv"  DSD MISSOVER;

      input date :MMDDYY9. open high low close volume adjclose RETURN month box logprice actdiff;

    format date mmddyy10.;

run;

data test1;

set test;

mm = month (date);

year = year (date);

keep mm year return date;

run;

options validvarname=upcase;

goptions ftext='Arial' htext=2 gunit=pct;

symbol1 v=dot i=join;

axis1 label=(angle=90 "Variance");

title h=4 "Yearly Variance";

footnote j=right "Source: FTSE Data-UK";

proc gplot data = test1 ;

by year ;

plot return*date/ overlay;

run;

[malakaext]

Hi..

I figure out how to do the line graph...

Thanks..