How to export results from regression in EG to Excel?

[SMR]

Hi,

Could anyone tell me if it is possible to export results that you get from linear regression in EG 4 to Excel, so that one could do further analysis?

Many thanks,

[Cynthia_sas]

Hi:
When I run the Linear Regression task on SASHELP.FITNESS file, I get 4 separate tables in the output HTML file. The 4 tables are:
[pre]
Label: Number of Observations
Label: Analysis of Variance
Label: Fit Statistics
Label: Parameter Estimates
[/pre]

So, if you want to get these 4 tables into Excel, you can take advantage of the fact that Excel knows how to read HTML files. If you right-click on the HTML icon, you can select Open with Internet Explorer to open the HTML file in a separate IE window. You will see that the HTML file is in a temporary Internet cache location. You can go to the IE file menu and save this HTML file to a location that is easy to remember, then switch over to Excel and open the HTML file using File --> Open menu in Excel.

On the other hand, you may want something "more" from the regression than just these 4 tables. The Linear Regression task gives you a way to create a SAS data set from the regression. When you make selections in the Linear Regression task, click on the choice for Predictions in the navigation pane. It is on this task pane that you can create an output dataset. By default, the output dataset is put into your SASUSER library with a very long name -- you might want to change the library location or the name so you can find the dataset again. However, once the regression has been run and the dataset has been created, in your Project Explorer, the icon for the output dataset from the regression will look like a a table with a red track ball in the lower right hand corner. If you click on that dataset icon, with a right mouse click, you can select Send To--> Excel.

cynthia

[SMR]

Hi,

Thanks for tips on opening html output using Excel. It has solved my problem for now.

My interest is on the table "Parameter Estimates". What I am looking for is to generate these parameter estimate outputs in a dataset format (I might have more than 10 "Parameter Estimate" tables), so that I would be able to extract data from these 10 number of parameter estimate tables, and rearrange them to create graphs in Enterprise Guide. In my case, these "parameter estimate" values might keep changing in different runs, thus would change graphs as well.

I have looked into "Prediction" option from the navigation pane, it is not possible to create these outputs in dataset from this option.

[Cynthia_sas]

If you want to create a dataset from just the parameter estimates output object you can get that output by using the ODS OUTPUT statement. The ParameterEstimate output object can be sent to an output dataset in the following manner:
[pre]
** use ODS OUTPUT to make a data set;
ods output ParameterEstimates=work.parmest;

PROC REG DATA=sasuser.fitness;
Linear_Regression_Model: MODEL age = rstpulse
/ SELECTION=NONE;
RUN;
QUIT;

** see what is in the data set;
proc print data=work.parmest;
title 'Parameter Estimate Data Set from Proc Reg';
run;
[/pre]

And, then, once the file is in a SAS dataset, you could either send to Excel from EG, or as long as you're going to write code for ODS OUTPUT, you could just write the EXPORT step:
[pre]

** now export the data set;
proc export data=work.parmest
outfile= "parmest_exp.xls"
dbms=excel2000 replace;
sheet="ParmEst";
run;
[/pre]

If you are using SAS 9.1.3 and PROC REG, then you should know that ODS GRAPHICS capabilities are built into PROC REG. So, if, for example, you wanted to see the default graphs available with PROC REG, you could do:
[pre]
ods html path='c:\temp' (url=none)
gpath='c:\temp' (url=none)
file='odsgraf.html'
style=analysis;

ods graphics on;

title 'experimental ODS Graphics facility';

PROC REG DATA=sasuser.fitness;
Linear_Regression_Model: MODEL age = rstpulse
/SELECTION=NONE;
RUN;
QUIT;

ods graphics off;
ods html close;
[/pre]

ODS Graphics is experimental in SAS 9.1.3 and is scheduled to go into production in SAS 9.2. Check it out, it's very cool!

cynthia

[SMR]

Hi again,

I am still having problem in getting results from regression in data set format:

1. I used ODS OUTPUT statement to produce table of parameter estimates of regression results. However, it has not produced outputs in dataset, as I wanted. I am getting now a seperate table of parameters estimates, but now included at the bottom of the same HTML output of regression results.


2. Next, I tried to export this table in excel format using following statement, but does not seem to work.

102 /* Start of custom user code. */
103 PROC EXPORT DATA = work.parmest OUTFILE = 'c:\parmest.xls' DBMS = EXCEL2002 REPLACE;
ERROR: DBMS type EXCEL2002 not valid for export.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Thank you very much.

[Cynthia_sas]

Hi:
I believe the answer to #1 is for you to just remove the PROC PRINT from your code node. The data set will still be created -- what you're seeing at the bottom of your HTML output (after PROC REG) are the results of the PROC PRINT step. If you don't want to see it, then just get rid of it, now that you know how the data set looks.

As for #2, I believe, as described here
http://support.sas.com/techsup/unotes/SN/006/006976.html
that you are getting that message because SAS/Access to PC File Formats may not be licensed on the server machine (where EG is executing the code). In this Tech Support Note, the workaround is to create a CSV file, which for your example from PROC REG, would look like this:
[pre]

proc export data=work.parmest
outfile='c:\temp\parmest.csv'
dbms=CSV
replace;
run;

[/pre]

Your site may have licensed SAS/Access for PC File Formats (PCFF) but it is possible that the SAS/Access package was not installed on your server or installed on the server where you need it. To find out whether this is the case, you should contact the people who configure and install SAS at your company. If SAS/Access for PCFF has not been licensed by your site, but you're interested in obtaining a license, you should contact your sales representative.

cynthia

[SMR]

Thanks for your reply.

I can see the data set created in the folder \work, but the problem is I am having it does not appear in the process flow diagram itself. To do further calculation from this data set, I would need to import it again. I seem to miss something here.

[ChrisHemedinger]

Going back to your original question -- how do you get parameter estimates into data tables for further analysis...

You can use the OUTEST option like so:

[pre]
PROC REG DATA=egsample.fitness outest=param;
First: MODEL age = rstpulse / SELECTION=NONE;
Second: MODEL age = runpulse / SELECTION=NONE;
RUN;
QUIT;
[/pre]

That will produce a record for each model you include. You can then export to Excel to manipulate if you want, but since you plan to turn around and use it as input into a graph task in EG, there is probably a way to manipulate it within EG, using a task, the query builder, or another SAS program to get it into the shape you need it.

Chris