How to select all variables from a table except 1 to run regression analysis

[Shone]

Hi as mentioned in the subject, how can I select all the variable from a table to run the regression. I can't manually type 100s of variables. Is there a way to get all the variable except 1 I am using as a depended variable?

If  I were to manually type it would look like this... how can I automatically select all the variables from a table except 1 or few?

proc logistic data= testdata;
model depvar = test1 test2 apple1 ball .... cat;
run;

Thanks, 

Shone

[PaigeMiller · Accepted solution]

Hi! So yes, you can select all the other variables in a data set for use in regression or logistic regression.

But before I get to that, I consider this a VERY BAD idea, because these hundreds of x-variables will be correlated with one another and so you will be badly overfitting the model; and the coefficients of the regression will have HUGE variances.

But ... here's how you select all the variables except the Y variable.

proc contents data=testdata noprint out=_contents_;
run;

proc sql noprint;
    select name into :names separated by ' ' from _contents_ where upcase(name)^='DEPVAR';
quit;

From there, instead you could use the macro variable just created, &names, as the right hand side of the model statement. But don't do that. Go ahead and read up on variable selection methods, and use those; or use your subject matter judgement (or your client's subject matter judgment) to pick at most 10 variables that are likely to be the most predictive.

The best solution, IMHO, is not programmed in SAS unfortunately (but is programmed in R) is to perform Logistic Partial Least Squares regression, in which case you can use all of the variables. Here is a reference: https://cedric.cnam.fr/fichiers/RC906.pdf. For continuous Y-variables, use PROC PLS in SAS.

[Shone]

Thank you so much. I agree we might not want to take all the variables but this dataset contains all the variable needed for analysis. 

[massaaki]

Although u said that is a very bad idea, it helped me too much. Tks!

[Reeza]

Depends, here are some different ways you can create the list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html?...

[HB]

Did you try it almost like you have it?

proc logistic data= testdata;
     model depvar = test1--cat;
run;

[Shone]

Can you please let me know how do I account that depvar could be in the middle of the dataset. Please note that depvar is also in the dataset. If I used the test1- cat it will take depvar as well

[Reeza]

Similar approach, but using SASHELP tables. 

proc sql noprint;
select name into :var_list separated by " " from sashelp.vcolumn where upcase(libname)='SASHELP' and upcase(memname)='CARS'
and upcase(name) ne ('INVOICE');;
quit;

%put &var_list;