@SteveDenham I ran my code on 3 variables over 250k random obs: PROC IMPORT OUT= vars DATAFILE= './summary.xls'
DBMS=xls REPLACE;
GETNAMES=YES;
RUN;
/* Use PROC SQL to create a macro variable (MissingVarList) that contains
the list of variables that have a property such as missing values */
RSUBMIT;
proc sql noprint;
select Variable,NLevels into :VarList separated by ' ', :nlevel
from vars
where NLevels = 3;
quit;
%put &=VarList;
/* 1. transpose from wide (Y, X1 ,...,X10000) to long (varNum VarName Y Value) */
data Long;
set proj.original; /* <== specify data set name HERE */
array x [*] &VarList; /* <== specify explanatory variables HERE */
do varNum = 1 to 3;
VarName = vname(x[varNum]); /* variable name in char var */
Value = x[varNum]; /* value for each variable for each obs */
output;
end;
drop &VarList;
run;
/* 2. Sort by BY-group variable */
proc sort data=Long; by VarName; run;
ods graphics off;
ods exclude all;
proc glm data=Long outest=PE;
CLASS Value;
by VarName;
model target = Value / RSQUARE;
quit;
ods exclude none;
/* Look at the results */
proc print data=PE(obs=5 keep=VarName Intercept Value _RSQ_);
run;quit; here's my log file after transpose (summary importing is working correctly): 31 /* 1. transpose from wide (Y, X1 ,...,X10000) to long (varNum VarName Y Value) */ 54 data Long; 55 set proj.original; /* <== specify data set name HERE */ 56 array x [*] &VarList; /* <== specify explanatory variables HERE */ 57 do varNum = 1 to 3; 58 VarName = vname(x[varNum]); /* variable name in char var */ 59 Value = x[varNum]; /* value for each variable for each obs */ 60 output; 61 end; 65 drop &VarList; 66 run; NOTE: There were 250000 observations read from the data set PROJ.ORIGINAL. NOTE: The data set WORK.LONG has 750000 observations and 10027 variables. NOTE: DATA statement used (Total process time): real time 17.61 seconds cpu time 17.61 seconds 68 69 /* 2. Sort by BY-group variable */ 70 proc sort data=Long; by VarName; run; NOTE: There were 750000 observations read from the data set WORK.LONG. NOTE: The data set WORK.LONG has 750000 observations and 10027 variables. NOTE: PROCEDURE SORT used (Total process time): real time 38.00 seconds cpu time 59.61 seconds 71 72 73 ods graphics off; 74 ods exclude all; 75 proc glm data=Long outest=PE; ------ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ALPHA, DATA, MANOVA, MULTIPASS, NAMELEN, NOPRINT, ORDER, OUTSTAT, PLOTS. ERROR 76-322: Syntax error, statement will be ignored. 76 CLASS Value; 77 by VarName; 78 model target = Value / RSQUARE; ------- 22 202 ERROR: No data set open to look up variables. NOTE: The previous statement has been deleted. ERROR 22-322: Syntax error, expecting one of the following: ;, ALIASING, ALPHA, CLI, CLM, CLPARM, COVBYCLASS, E, E1, E2, E3, E4, EST, I, INTERCEPT, INVERSE, NOINT, NOUNI, P, PREDICTED, SINGULAR, SOLUTION, SS1, SS2, SS3, SS4, TOLERANCE, X, XPX, ZETA. ERROR 202-322: The option or parameter is not recognized and will be ignored. 79 quit; NOTE: PROCEDURE GLM used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 80 ods exclude none; 81 91 92 /* Look at the results */ 93 proc print data=PE(obs=5 keep=VarName Intercept Value _RSQ_); ERROR: File WORK.PE.DATA does not exist. 94 run; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE PRINT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 94 ! quit; ID TARGET VAR1 ... VAR10000 VarNum VarName Value 1 yyy xx xx 1 Var1 var1_level1 2 yyy xx xx 2 Var2 var2_level1 3 yyy xx xx 3 Var3 var3_level1 4 yyy xx xx 1 Var1 var1_level2 5 yyy xx xx 2 Var2 var2_level2 6 yyy xx xx 3 Var3 var3_level1 7 yyy xx xx 1 Var1 var1_level1 8 yyy xx xx 2 Var2 var2_level2 9 yyy xx xx 3 Var3 var3_level2
... View more
