Variable not found

[duckypooh]

Hello all! 

I have an error: variable not found. 

libname Analysis '/folders/myfolders/research';

proc import out= Analysis.DetailedCMA datafile= "/folders/myfolders/Research/Analysis 1906/CMA 1906.xlsx" 
DBMS=xlsx Replace; SHEET="Detailed CMA"; 
GETNAMES=YES;
RUN;

data import; set import;

title 'Summary statistics';
proc means data=import ndec=2 n mean std median p25 p75 min max; var BMI;  run;

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         title 'Summary statistics';
 74         proc means data=import ndec=2 n mean std median p25 p75 min max; var BMI;  run;
 ERROR: Variable BMI not found.
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE MEANS used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 75         
 76         
 77         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 89         

I have also tried creating a new variable: BMI_1 on the excel sheet where BMI_1 = BMI *1, to convert all values in BMI to numeric. But SAS cant recognize BMI_1 either. 

[Kurt_Bremser]

Inspect the dataset to see how the columns were imported.

[duckypooh]

Sorry for my basic question but how do I do that?

[Jagadishkatam]

Please check by opening the dataset which you imported and see if there us a variable by name bmi.

[duckypooh]

Hello, 

I have checked and it seems like when importing the dataset, some variables were renamed from their excel version. E.g. 'Age' on excel became 'AA' etc. How can i change the variables back to their original names? 

[Kurt_Bremser]

---

@duckypooh wrote:

Hello, 

I have checked and it seems like when importing the dataset, some variables were renamed from their excel version. E.g. 'Age' on excel became 'AA' etc. How can i change the variables back to their original names? 

---

Make sure column names are in row 1, and that data starts in row 2 in the spreadsheet.

[Kurt_Bremser]

libname Analysis '/folders/myfolders/research';

This creates a library reference for the path you specified.

Inspect the log to see if it worked.

proc import out= Analysis.DetailedCMA datafile= "/folders/myfolders/Research/Analysis 1906/CMA 1906.xlsx" 
DBMS=xlsx Replace; SHEET="Detailed CMA"; 
GETNAMES=YES;
RUN;

This tries to import a file in a different path (note the capital R in Research) to this library. Inspect the log to see if the file was successfully imported.

data import; set import;

This is a data step that does essentially nothing, except wasting time. It just overwrites a dataset in WORK with itself (if it existed at all). Remove it.

title 'Summary statistics';
proc means data=import ndec=2 n mean std median p25 p75 min max; var BMI;  run;

This tries to run summary statistics on the dataset in WORK (not on the dataset you tried to import in the first step!).

Bottom line: inspect the log to see what worked; fix problems from the top down, and make sure you are consistent in using libraries and dataset names.

[Reeza]

Comment your code with what you think is happening to avoid issues like this. 

When you import your data you're calling it : analysis.detailedCMA. However, when you try and use it, you're trying to use some data set called IMPORT instead? Is that a data set from somewhere else, or were you intending to use the CMA data?

The SAS programming classes are free and I would highly recommend taking some time to go through it if you're trying to learn SAS, especially if you're not familiar with the basic concepts yet.

*assigns a location where you can save your files between sessions;
libname Analysis '/folders/myfolders/research';

*imports the data;
*data is saved to analysis.detailedCMA;
proc import out= Analysis.DetailedCMA datafile= "/folders/myfolders/Research/Analysis 1906/CMA 1906.xlsx" 
DBMS=xlsx Replace; SHEET="Detailed CMA"; 
GETNAMES=YES;
RUN;

*not sure what this is doing?????;
*data import; set import;

*Assume you intended this to summarize your analysis.detailedCMA data;
*it would run on any numerical variable;
title 'Summary statistics';
proc means data=analysis.detailedCMA ndec=2 n mean std median p25 p75 min max; var BMI;  run;

Here's an example on calculating BMI and adding that to a data set.

    data class;
    	set sashelp.class;
    	length category $20.;
    	bmi = 703*(weight/(height**2));

    	if bmi < 18 then
    		category='Under Weight';
    	else if 18 <= BMI < 25 then
    		category='Normal';
    	else if 25 <= BMI < 30 then
    		category ='Over Weight';
    	else if BMI >=30 then
    		category = 'Obese';
    run;