Data visualization with SAS programming

Help with SAS code

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

Help with SAS code

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

Attachment

Accepted Solutions
Solution
‎09-08-2012 04:20 PM
Valued Guide
Posts: 763

Re: Help with SAS code

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=_Smiley Happy 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;

View solution in original post

Attachment

All Replies
Solution
‎09-08-2012 04:20 PM
Valued Guide
Posts: 763

Re: Help with SAS code

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=_Smiley Happy 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;

Attachment
Frequent Contributor
Posts: 91

Re: Help with SAS code

Wow... that's amazing...

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

Valued Guide
Posts: 763

Re: Help with SAS code

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.

Frequent Contributor
Posts: 91

Re: Help with SAS code

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

Valued Guide
Posts: 763

Re: Help with SAS code

Frequent Contributor
Posts: 91

Re: Help  with SAS code

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

Valued Guide
Posts: 763

Re: Help  with SAS code

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=_Smiley Happy 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;

Frequent Contributor
Posts: 91

Re: Help  with SAS code

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

Valued Guide
Posts: 763

Re: Help  with SAS code

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?

Frequent Contributor
Posts: 91

Re: Help  with SAS code

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 Smiley Sad

Thanks

Valued Guide
Posts: 763

Re: Help  with SAS code

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;

Attachment
Attachment
Frequent Contributor
Posts: 91

Re: Help  with SAS code

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;

Frequent Contributor
Posts: 91

Re: Help  with SAS code

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;

Frequent Contributor
Posts: 91

Re: Help with SAS code

Hi..

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

Thanks..

☑ This topic is SOLVED.

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

Discussion stats
  • 15 replies
  • 470 views
  • 3 likes
  • 2 in conversation