Date: Wed, 14 Jan 2009 12:16:08 -0600 "./ ADD NAME=Data _null_," "SAS(r) Discussion" "./ ADD NAME=Data _null_," Re: Excel editable graphs with SAS To: Erwan LE DU <2c8557a00901140158h7673e58cr452e1d448984a7c9@mail.gmail.com> text/plain; charset=ISO-8859-1

This is a VERY simplistic approach but could be fancied up to be somewhat general. Perhaps you could adapt this to your needs.

title1 'Plot of Weight vs Height from SASHELP.CLASS'; data _null_; length script filevar command value text \$256; script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs'); filevar = script;

/* write the script */ file dummy1 filevar=filevar; put 'Const xlXYScatterLines = 74'; put 'Const xlXYScatter = -4169'; put 'Const xlColumns = 2'; put 'Const xlRows = 1'; put 'Set objExcel = CreateObject("Excel.Application")'; put 'objExcel.Visible = True'; put 'objExcel.DisplayAlerts = False'; put 'Set objWorkbook = objExcel.Workbooks.Add()'; put 'Set objWorksheet = objWorkbook.Worksheets(1)';

array col[2] \$8 ('Height','Weight'); do j = 1 to dim(col); put 'objWorksheet.Cells(1,' j +(-1) ') = ' col[j]:\$quote10.; end; do i = 2 by 1 until(end); set sashelp.class end=end; do j = 1 to dim(col); if vtypeX(col[j]) eq 'C' then value = quote(strip(vvalueX(col[j]))); else value = strip(vvalueX(col[j])); put 'objWorksheet.Cells(' i +(-1) ',' j +(-1) ') = ' value; end; end;

label height = 'Height (inches)' weight = 'Weight (pounds)';

put 'Set objRange = objWorksheet.UsedRange'; put 'objRange.Select'; put 'Set colCharts = objExcel.Charts'; put 'colCharts.Add()'; put 'Set objChart = colCharts(1)'; put 'objChart.Activate'; put 'objChart.ChartType = xlXYScatter'; put 'objChart.PlotBy = xlColumns'; put 'objChart.HasLegend = False'; put 'objChart.HasTitle = True'; set sashelp.vtitle(where=(type eq 'T' and number eq 1)); put 'objChart.ChartTitle.Text = ' text:\$quote256.; do i = 1 to dim(col); text = vlabelX(col[i]); put 'objChart.Axes(' i +(-1) ').HasTitle = True'; put 'objChart.Axes(' i +(-1) ').AxisTitle.Characters.Text = ' text:\$quote256.; end;

/* close the script file by opening another, not used */ filevar = catx('\',pathname('WORK'),'DUMMY.vbs'); file dummy1 filevar=filevar;

/* look at the script, not necessary but may be useful */ infile dummy2 filevar=script end=eof; do _n_ = 1 by 1 until(eof); input; putlog _n_ z4. +1 _infile_; end;

/* call the script */ command = catx(' ','cscript',quote(strip(script))); infile dummy3 pipe filevar=command end=eof; do until(eof); input; putlog _infile_; end; stop; run;

On 1/14/09, Erwan LE DU <dotker@gmail.com> wrote: > Hi everyone, > > Does anyone know whether it is possible to create editable Excel graphs from > SAS, ie not an image file. > > Thanks, > > Erwan >

Back to: Top of message | Previous page | Main SAS-L page