- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am trying to duplicate the Excel LINEST function in a SAS EG project. This Excel function returns the "slope of best fit" using the "Least Square Reqression Line Equation" method. I do not need to produce a graph, but only the numerical value of the slope as denoted in the Excel results.
The table has 4 fields;
Name of Agency 12 13 14
As an example., here are values in the 12, 13, and 14 fields:
12: 2
13: 1.5
14: 3
In the Excel file the LINEST function returns a slope value of.5 for these three data points. I need help:
1. Determining which SAS EG Regression function will duplicate these calculations
2. Assistance in how to enter the field names into the function itself (Dependent variable, Explanatory variable, etc.). I had no luck using the "noodle around" method of problem-solving.
Thanks so much for assistance.
William
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Tasks>Data>Transpose
Transpose variables - 12/13/14
Group Analysis by Agency
Now you'll have
Briarwood 12 2
Briarwood 13 3
Briarwood 14 1
Tinseltown 12 1.5
Tinseltown 13 2.5
Tinseltown 14 3
Woodcreek 12 4
Woodcreek 13 3
Woodcreek 14 2
Ashton 12 3.5
Ashton 13 1
Ashton 14 2
Except for the fact that 12/13/14 are character because variable names are always character. They may have an underscore in front as well.
Use Query Builder to convert to numeric in a calculated column. In my case I had an underscore in front of the name so I used input(compress(source, "_"), 2.)
Then Use Linear Regression with
Dependent as Y (12, 13, 14)
Explanatory as X (2, 3, 1)
Group by as Agency
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You need to transpose your data
Tasks>Data>Transpose
This is because SAS expects your data to be in the form
X Y
12 2
13 1.5
14 3
Then you can use linear regression, Task>Regression>Linear Regression
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I re-formatted the data to match your recommendation. This yields three variables:
Name_of_Agency X Y
I used the data in Analysis>Linear Regression but the results clearly show I have not entered the variables in the correct spots in the Linear Regression Data window. The Dependent Variable appears to require a number since it will not accept the Name_of_Agency variable. I have tried various combinations of these three variables, but none produces a slope of line value based on the X and Y data for the agencies. Ideas? Thanks.
PS. I had to stack the data to create the X and Y variables in the format you provided above.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Forget what excel did/does.
What are you trying to achieve? Name of Agency is a character variable and it makes sense that you can't perform a regression using a character variable.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am trying to get SAS to return a numerical value representing the slope of best fit based on three data points for a list of agencies. Like this:
Agency | 12 | 13 | 14 | Slope |
---|---|---|---|---|
Briarwood | 2 | 3 | 1 | -0.5 |
Tinseltown | 1.5 | 2.5 | 3 | .75 |
Woodcreek | 4 | 3 | 2 | -1 |
Ashton | 3.5 | 1 | 2 | -.75 |
The numbers in the Slope column are "real" based on the three data points and using the "Least Squares" method of calculating slope of line.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Tasks>Data>Transpose
Transpose variables - 12/13/14
Group Analysis by Agency
Now you'll have
Briarwood 12 2
Briarwood 13 3
Briarwood 14 1
Tinseltown 12 1.5
Tinseltown 13 2.5
Tinseltown 14 3
Woodcreek 12 4
Woodcreek 13 3
Woodcreek 14 2
Ashton 12 3.5
Ashton 13 1
Ashton 14 2
Except for the fact that 12/13/14 are character because variable names are always character. They may have an underscore in front as well.
Use Query Builder to convert to numeric in a calculated column. In my case I had an underscore in front of the name so I used input(compress(source, "_"), 2.)
Then Use Linear Regression with
Dependent as Y (12, 13, 14)
Explanatory as X (2, 3, 1)
Group by as Agency
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you!! Your instructions were A+ in clarity and accuracy. The Linear Regression object is now running without issue. Really, really appreciate you hanging in on this.
I googled this topic upside down and sideways and the only articles I found were for Base SAS, not SAS EG. Might I ask how you knew this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I did think of one more related issue I'm stumped on. The results look like a One-Way Freq page (SAS Report). Unlike the latter, however, there does not appear to be a "Results" option that will create a data table. Is it possible to specify a data table format?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Welcome to the stage where EG gets you 90% there and then ...
At least in my version, EG 5.1
You can do it via code. Determine the name of your dataset and modify appropriately:
I may have had the X/Y backwards in my initial instructions by the way.
proc sort data=have;
by agency;
run;
proc reg data=have;
by agency;
model y=x;
ods output parameterestimates=want;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, they were backwards. When the results didn't match the Excel output I knew there was an issue. I switched the X and Y roles and the results were identical to Excel.
Thanks for the code. I will post once I've had a chance to try it out. Thanks much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yea, success!
This thread has excellent guidance that took a good deal of valuable time and effort on your part--what a superlative effort to help a complete stranger. I would have never figured this out on my own. THANKS!!