SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LiamRowan
Calcite | Level 5

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

Thanks so much for  assistance.

William

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

10 REPLIES 10
Reeza
Super User

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

LiamRowan
Calcite | Level 5

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.

Reeza
Super User

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.

LiamRowan
Calcite | Level 5

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:

Agency121314Slope

Briarwood

231-0.5
Tinseltown1.52.53.75
Woodcreek432-1
Ashton3.512-.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

Reeza
Super User

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

LiamRowan
Calcite | Level 5

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?

LiamRowan
Calcite | Level 5

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?

Reeza
Super User

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;

LiamRowan
Calcite | Level 5

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!

LiamRowan
Calcite | Level 5

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!!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 2862 views
  • 3 likes
  • 2 in conversation