topic Joining and reshaping tables in PROC GLM in SAS Programming

Joining and reshaping tables in PROC GLM

mh2t — Wed, 24 Jun 2020 15:53:15 GMT

How can I get an output from PROC GLM to be like below:

VarName RSQUARE height_MEAN Coeff Var
weight xx xxx ww
sex yy yyy www

It is easy in proc reg but I don't know how to do it in proc glm.

Thank you for your response.

Re: Joining and reshaping tables in PROC GLM

Reeza — Wed, 24 Jun 2020 16:06:47 GMT

You have an RSquare for each variable? How does that work?

Re: Joining and reshaping tables in PROC GLM

mh2t — Wed, 24 Jun 2020 16:12:12 GMT

so we run two univariate glm models, each has a separate RSquare in the output.

Re: Joining and reshaping tables in PROC GLM

Reeza — Wed, 24 Jun 2020 16:15:48 GMT

Do you have code to already run all those regressions? If we can add our solution to yours that's more efficient.

Re: Joining and reshaping tables in PROC GLM

mh2t — Wed, 24 Jun 2020 16:23:30 GMT

/* 1. transpose from wide (Y, X1 ,...,X100) to long (varNum VarName Y Value) */
data Long;
set Wide;                       /* <== specify data set name HERE         */
array x [*] x1-x&nCont;         /* <== specify explanatory variables HERE */
do varNum = 1 to dim(x);
   VarName = vname(x[varNum]);  /* variable name in char var */
   Value = x[varNum];           /* value for each variable for each obs */
   output;
end;
drop x:;
run;

/* 2. Sort by BY-group variable */
proc sort data=Long;  by VarName;  run;


/* 3. univariate glm regression on single predictor*/
proc glm data=Long outstat=PE;
   CLASS Value;
   by VarName;
   model Y = Value;
   ods select FitStatistics;
quit;

proc print data=PE;
run;quit;

Re: Joining and reshaping tables in PROC GLM

Reeza — Wed, 24 Jun 2020 16:45:20 GMT

/* 3. univariate glm regression on single predictor*/
proc glm data=Long outstat=PE;
   CLASS Value;
   by VarName;
   model Y = Value;
   *ods select FitStatistics;
ods output fitstatistics = rSquared parameterEstimates = PE(where=(Parameter='Value'));
quit;

This will capture the data for you in a clean fashion into two data sets rSQuared and PE. You can then merge them to get the table you want. I think for means you'll need a PROC MEANS for mean.

Re: Joining and reshaping tables in PROC GLM

mh2t — Wed, 24 Jun 2020 17:32:36 GMT

Thank you @Reeza. I'm very new to SAS environment, I don't know how to merge them.

Re: Joining and reshaping tables in PROC GLM

Reeza — Wed, 24 Jun 2020 17:52:57 GMT

Here's a great tutorial on merging.
https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-in-sas/