@Tom
1. I was wrong about your prior code, SAS does not like the trimmed command as you have it written
2. I should also mention that I entered the incorrect weight variable in the surveyreg procedure which should read PERWT10F.
3. One or both of these changes also helped:
WHERE LABEL1 = "R-Square" instead of "R-SQUARE"
SQRT(INPUT(cValue1,8.)) instead of CVALUE1
4. SAS didn't like order by ABS(R) in the last create table procedure, so I made changes accordingly.
/* download zip file to documents folder and extract https://meps.ahrq.gov/mepsweb/data_files/pufs/h138ssp.zip*/
/* MEPS HC-138: 2010 Full Year Consolidated Data File
https://meps.ahrq.gov/mepsweb/data_stats/download_data_files_detail.jsp?cboPufNumber=HC-138*/
FILENAME H138 'c:\Users\John\Downloads\H138.ssp';
PROC XCOPY in = H138 out = WORK IMPORT;
RUN;
/* The number of predictor variables (nvar) is the number of iterations
SAS will use in the DO LOOP later on in the program. */
PROC SQL;
SELECT NVAR INTO :NVAR
FROM DICTIONARY.TABLES
WHERE LIBNAME='WORK' AND MEMNAME='H138';
QUIT;
/* PROC CONTENTS is then used to obtain a list of the predictor variable names, which are then stored as macro
variables using the PROC SQL SELECT INTO statement: */
PROC CONTENTS DATA=WORK.H138 OUT=CONTENTS NOPRINT;
RUN;
PROC SQL NOPRINT;
SELECT nliteral(NAME) INTO :VAR1-:VAR76
FROM WORK.CONTENTS
;
%let nvar=&sqlobs;
QUIT;
/* Next, an empty table is created to store the output from PROC SURVEYREG as it is inserted one row at a time. */
PROC SQL;
CREATE TABLE SURVEYCORR
(PARAMETER CHAR(15), R_SQUARE CHAR(8), R NUM(8), PROBT NUM(8));
QUIT;
/*
PROC SURVEYREG uses the survey design variables in its strata, cluster, and weight statements as do all the
other survey procedures. It also has the option to include an ODS OUTPUT statement to store parameter estimates, fit statistics, and other information created when the model runs. The R-square value is extracted from the
FitStatistics output using PROC SQL, while p-values and the sign of the estimated regression coefficient are extracted from the ParameterEstimates output. The square root function is used to get the correlation coefficient,
combined with the sign of the regression coefficient to tell us the direction of the correlation (negative or positive)
with the target variable. The target variable itself is input as a parameter when the macro is called, so the process can be re-run for multiple target variables of interest by changing the parameter, re-compiling, and re-running
the macro. */
OPTIONS MPRINT;
%MACRO CORR(TARGET=);
PROC SURVEYREG DATA=WORK.H138;
STRATA VARSTR;
CLUSTER VARPSU;
WEIGHT PERWT10F;
MODEL &TARGET=&&VAR&I /SOLUTION;
ODS OUTPUT PARAMETERESTIMATES=PARAMETER_EST FITSTATISTICS=FIT;
RUN;
PROC SQL;
INSERT INTO SURVEYCORR
SELECT
PARAMETER
,CVALUE1 AS R_SQUARE
,SIGN(ESTIMATE)* SQRT(INPUT(cValue1,8.)) AS R
,PROBT AS PVALUE
FROM FIT
,PARAMETER_EST
WHERE LABEL1 = "R-Square"
AND PARAMETER = "&&VAR&I";
QUIT;
%MEND CORR;
/* The process above will run for each predictor variable, inserting a new row in the table each time, once it is called
within the following loop: */
%MACRO LOOP;
%DO I=1 %TO &NVAR;
%CORR(TARGET=PUBAT10X);
%END;
%MEND LOOP;
%loop ;
PROC SQL;
CREATE TABLE WORK.SURVEYCORR_out AS
SELECT
PARAMETER
,R_SQUARE
,ABS(R) as ABS_R FORMAT BEST6.4
,PROBT AS PVALUE FORMAT PVALUE6.4
,CASE WHEN PROBT <=0.05 THEN "YES" ELSE "NO" END AS SIGNIFICANT_95
FROM SURVEYCORR
WHERE PARAMETER NOT IN ('DUPERSID','VARSTR','VARPSU','PERWT10F')
ORDER BY ABS_R DESC;
QUIT;
