How to Reg on each row?! with Slope/Intercept saved out?!

[hellohere]

I have,FOR EACH ROW, Y-values as columns and X-Values are the numeric[100 80 60 40 30 20 10 5] at the column names.

How to run PROC REG and save out the SLOPE and INTERCEPT for each?! Thanks.

Dataset attached below.

[Quentin]

**EDIT** I may have completely misunderstood your question.  Can you describe your data, and show the regression you want to run?

What are the values [100 80 60 40 30 20 10 5] ?  Are they counts? or Times?

Regardless, you should transpose your data to be vertical.  It would have three columns, X Y Group.  Where group is [100 80 60 40 30 20 10 5].

Then you could run PROC REG or whatever with a BY statement for group.

Rather than post a SAS dataset, it would be more helpful to post sample data a DATA step using the CARDS statement.  You should also post the code you have tried, that will help people help you.

[hellohere]

x=[100 80 60 40 30 20 10 5] always

Y=[453 459 466 472 480 482 486 486] for the first row ... and so on

[Quentin]

Agree with @PaigeMiller , use the same transpose approach provided in the other thread.  Then you can run your regression like:

proc reg data=have;
  model y=x;
  by xahead;
run;

With statements added to output the parameters you want.

[PaigeMiller]

Transpose the data set and then remove the _100 _80 etc. into a numeric variable. That's the only way. @Ksharp already showed you how to do this here: https://communities.sas.com/t5/SAS-Programming/How-to-line-plot-all-info-without-transpose/m-p/98230... In fact, the data set created in that thread should be usable in the regression problem.

Repeating what I said in that thread:

"Best practice is to NOT arrange the data like this in the first place. Specifically, you have incorporated some meaningful data that the plot needs in the variable name — in this case, specifically, the data in the variable name is the _100 and _80 and so on. Data belongs in variable values, not in the variable name."

[jiltao]

See if the following program does what you wanted --

libname jt 'c:\temp';

proc transpose data=jt._reg_by_row out=new(rename=(col1=y));
  by xahead;
 run;

data new;
    set new;
	x = input(compress(_name_,"off_min_ct_"),best.);
	drop _name_;
run;

proc reg data=new;
  by xahead;
  model y=x;
run;
quit;

Hope this helps,

Jill

Add tags

[jiltao]

libname jt 'c:\temp';

proc transpose data=jt._reg_by_row out=new(rename=(col1=y));
  by xahead;
 run;

data new;
    set new;
	x = input(compress(_name_,"off_min_ct_"),best.);
	drop _name_;
run;

proc reg data=new;
  by xahead;
  model y=x;
run;
quit;

Hope this helps,

Jill

[FreelanceReinh · Accepted solution]

---

@hellohere wrote:

How to run PROC REG and save out the SLOPE and INTERCEPT for each?! Thanks.

If slope and intercept is all you would need from PROC REG, you can also compute them in a DATA step:

data want(drop=i _:);
array x[8] (100 80 60 40 30 20 10 5);
set _reg_by_row;
array y[8] o:;
_x=mean(of x[*]);
_y=mean(of y[*]);
_s=0;
do i=1 to dim(x);
  _s+(x[i]-_x)*(y[i]-_y);
end;
slope=_s/css(of x[*]);
intercept=_y-slope*_x;
run;

But this is neither much shorter nor safer than relying on PROC REG after transposing.

[hellohere]

Thanks this surely works.  

I was looking from IML with matrix OP. 

[Ksharp]

Here is IML code/solution if you need it.

libname x v9 'C:\Users\xiakeshan\Documents\Downloads';
proc iml;
use x._t_mm_temp_coll_n(keep=off_max_ct:);
read all var _num_ into off_max_ct[c=vname];
close;
use x._t_mm_temp_coll_n;
read all var {xahead};
close;

parameters=j(nrow(xahead),2,.); /*Save Intercept and Slope parameter estimator*/
t_off_max_ct=t(off_max_ct);
x=j(nrow(t_off_max_ct),1,1)||t(inputn(scan(vname,-1,'_'),'best.'));
do i=1 to ncol(t_off_max_ct);
  y=t_off_max_ct[,i];
  parameters[i,]=t(solve(x`*x,x`*y));
end;

create want from xahead parameters[c={'xahead' 'Intercept' 'Slope'}];
append from xahead parameters;
close;
quit;

[PaigeMiller]

@FreelanceReinh brilliant. Especially the part where you say "But this is neither much shorter nor safer than relying on PROC REG after transposing." That last sentence is very important.

The whole idea of avoiding transposing and working with data in the inefficient wide arrangement (as apparently @hellohere would prefer) to create one's own formulas in a data step or IML, that SAS has already programmed into a PROC, is very inefficient in the long run. SAS has already done the hard work to make sure PROC REG gives correct answers, and then had the code tested against a bazillion real-world data sets. There's no need for a user to create his or her own code, that is inefficient in terms of time, and probably will often create wrong answers, and avoids the power of SAS, which has already been paid for by people's employer or university. So again, I urge the OP to avoid this wide layout of the data and instead use the long layout of the data, as not using wide data sets solves a lot of problems easily.

[Tom]

Transpose the data and run regression by the id variable, which looks like it is named XAHEAD.

%let x=100 80 60 40 30 20 10 5;
%let vars=eff_min_ct_%sysfunc(tranwrd(&x,%str( ),%str( eff_min_ct_)));

data tall;
  set have;
  array _x[%sysfunc(countw(&x))] _temporary_ (&x);
  array _y &vars;
  do over _x;
    x=_x;
    y=_y;
    output;
  end;
  drop &vars;
run;

proc reg data=tall;
  by xahead ;
  model y=x ;
run;

But you might want to talk to a statistician about better ways to analyze your data.

[hellohere]

I know how to transpose and save out paraestimates from PROC REG. Thanks. 

[Ksharp]

Here are two ways you can use to get these parameter estimator within a data set.

libname x v9 'C:\Users\xiakeshan\Documents\Downloads';
data x;
 set x._t_mm_temp_coll_n;
 array _x{*} off_max_ct_: ;
 do i=1 to dim(_x);
   y=_x{i};
   x=input(scan(vname(_x{i}),-1,'_'),best.);
   output;
 end;
 keep x y xahead;
run;


ods select none;
ods output   ParameterEstimates=  ParameterEstimates2;
proc reg data=x ;
by xahead;
model y=x;
quit;
ods select all;

libname x v9 'C:\Users\xiakeshan\Documents\Downloads';
data x;
 set x._t_mm_temp_coll_n;
 array _x{*} off_max_ct_: ;
 do i=1 to dim(_x);
   y=_x{i};
   x=input(scan(vname(_x{i}),-1,'_'),best.);
   output;
 end;
 keep x y xahead;
run;


ods select none;
proc reg data=x outest=ParameterEstimates;
by xahead;
model y=x;
quit;
ods select all;