There's a macro with a do loop I'm attempting to run, but I keep getting the following error:
WARNING: Apparent symbolic reference NVAR not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand
is required. The condition was: &nvar
ERROR: The %TO value of the %DO I loop is invalid.
Code is from this resource: https://www.lexjansen.com/nesug/nesug13/34_Final_Paper.pdf
/* 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\Documents\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 NAME INTO:VAR1-:VAR76
FROM WORK.CONTENTS;
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. */
%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
/*
WARNING: Apparent symbolic reference NVAR not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand
is required. The condition was: &nvar
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro LOOP will stop executing.
*/
/*
After calling the macro, PROC SQL is used to format the results, sort by correlation size, and exclude the survey
design variables from the tabulated output.
*/
PROC SQL;
CREATE TABLE WORK.SURVEYCORR_out AS
SELECT
PARAMETER
,R_SQUARE
,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;
You never told it how many variables there were.
Try fixing this step so that it also sets NVAR to the number of variables found. You can leave the upper bound on the range of names unnamed and it will work even if there are more than 76 variable names in your dataset.
You also might as well protect against variable names that do not follow normal SAS naming convention by using NLITERAL() function when generating the names into the macro variables.
PROC CONTENTS DATA=WORK.H138 OUT=CONTENTS NOPRINT;
RUN;
PROC SQL NOPRINT;
SELECT nliteral(NAME) INTO :VAR1- trimmed
FROM WORK.CONTENTS
;
%let nvar=&sqlobs;
QUIT;
You never told it how many variables there were.
Try fixing this step so that it also sets NVAR to the number of variables found. You can leave the upper bound on the range of names unnamed and it will work even if there are more than 76 variable names in your dataset.
You also might as well protect against variable names that do not follow normal SAS naming convention by using NLITERAL() function when generating the names into the macro variables.
PROC CONTENTS DATA=WORK.H138 OUT=CONTENTS NOPRINT;
RUN;
PROC SQL NOPRINT;
SELECT nliteral(NAME) INTO :VAR1- trimmed
FROM WORK.CONTENTS
;
%let nvar=&sqlobs;
QUIT;
@Tom Well, after entering your suggestions, the error is gone when i run %LOOP;, but the final table is empty.
NOTE: Table WORK.SURVEYCORR_out created, with 0 rows and 5 columns.
That is a different question.
Check the log from the top down to see why it is not generating any data. Perhaps you have some error and you that is making SAS stop saving any outputs.
Turn on the MPRINT option to see the SAS code that is generated by the %CORR() macro. (The %LOOP macro does not generate any SAS code, just more macro code).
Run the %CORR() macro for one of the variables and see if it works.
Run the SAS code you are trying to get to run for one variable without using any macro code to make sure that the code
you are using the macro code to generate is actually valid code.
The %LOOP macro looks silly also.
%MACRO LOOP;
%DO I=1 %TO &NVAR;
%CORR(TARGET=PUBAT10X);
%END;
%MEND LOOP;
Why run the exact same thing multiple times?
Shouldn't you be using the macro variables you created with the SQL INTO query before?
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;
9.4 TS1M5.
4728 PROC SQL NOPRINT;
4729 SELECT nliteral(NAME) INTO :VAR1- trimmed
-------
79
ERROR 79-322: Expecting a :.
yeah it probably thinks it is the name of the upper bound macro variable and is mad because it doesn't match the lower bound.
When you have Errors include the LOG with the procedure or data step code that generated the error along with all the notes, messages as well as the error. Copy all that text then paste into a text box opened on the forum with the </> icon.
If you are using macros then set OPTION MPRINT; before running the code so that the details of the generated code are shown and the error message appears relatively close the generated code that created the error.
Did you verify that the data set WORK.H138 was actually created? Did you unzip the downloaded Zip file?
You may need to share some of the log from the steps before your macro.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: