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
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
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
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.
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.
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
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
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?
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?
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;
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!
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: