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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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