ERROR: Variable BMI in list does not match type prescribed for this list.

duckypooh — Thu, 11 Jun 2020 02:45:50 GMT

Hi all,

I am a new SAS user and would like to seek your advice on this error; when i tried running a summary statistics with various variables like Age and Progestrone

This is my code:

libname mc '/folders/myfolders/research';

proc import out= mc.complieddata datafile= "/folders/myfolders/Research/Master List 07062020 SAS.xlsx" 
DBMS=xlsx Replace; SHEET="Data Complied From Both Studies"; 
GETNAMES=YES;
RUN;

data import; set research.import;

if Preg_Group in ('Miscarriage' 'Normal');

     if BMI >28        then BMI_cat = 'BMI >28   ';
else if 19 <= BMI < 23 then BMI_cat = '19<=BMI<23';
else                        BMI_cat = 'BMI Other ';

     if Age <32   then Age_cat = 'Age <32 ';
else if Age >= 32 then Age_cat = 'Age >=32';

ln_Progestrone = log(Progestrone_nmol_L);

run;

title 'Cross tabulation on BMI and Age category groups';
proc freq data=import; tables BMI_cat*Preg_Group/fisher; where BMI_cat ^= 'BMI Other '; run;
proc freq data=import; tables Age_cat*Preg_Group/fisher;  run;


run;

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

This was the log it generated:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         libname mc '/folders/myfolders/research';
 NOTE: Libref MC was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /folders/myfolders/research
 74         
 75         proc import out= mc.complieddata datafile= "/folders/myfolders/Research/Master List 07062020 SAS.xlsx"
 76         DBMS=xlsx Replace;
 76       !                    SHEET="Data Complied From Both Studies";
 77         GETNAMES=YES;
 78         RUN;
 
 NOTE:    Variable Name Change.  Preg Group -> Preg_Group                      
 NOTE:    Variable Name Change.  Subject number -> Subject_number                  
 NOTE:    Variable Name Change.  Progestrone nmol/L -> Progestrone_nmol_L              
 NOTE:    Variable Name Change.  Screen/Incl/Excl -> Screen_Incl_Excl                
 NOTE:    Variable Name Change.  Enrollment Date -> Enrollment_Date                 
 NOTE:    Variable Name Change.  GA in weeks -> GA_in_weeks                     
 NOTE:    Variable Name Change.  F/U Date 16th Wk -> F_U_Date_16th_Wk                
 NOTE:    Variable Name Change.  F/U Date 40th Wk -> F_U_Date_40th_Wk                
 NOTE:    Variable Name Change.  16 week miscarriage -> _16_week_miscarriage            
 NOTE:    Variable Name Change.  40 week miscarriage -> _40_week_miscarriage            
 NOTE:    Variable Name Change.  Chromosomal Aberration (Y/N) -> VAR18                           
 NOTE:    Variable Name Change.  Chromosomal Aberration (Descript -> Chromosomal_Aberration__Descript
 NOTE:    Variable Name Change.  Threatened Miscarriage (Y/N) -> VAR20                           
 NOTE:    Variable Name Change.  Spontaneous Miscarriage (Y/N) -> VAR21                           
 NOTE:    Variable Name Change.  IUGS & YS (Y/N) -> VAR22                           
 NOTE:    Variable Name Change.  Completed? -> Completed_                      
 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
       options MSGLEVEL=I.
 NOTE: The import data set has 170 observations and 24 variables.
 NOTE: MC.COMPLIEDDATA data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.25 seconds
       cpu time            0.17 seconds
       
 
 79         
 80         data import; set research.import;
 81         
 82         if Preg_Group in ('Miscarriage' 'Normal');
 83         
 84              if BMI >28        then BMI_cat = 'BMI >28   ';
 85         else if 19 <= BMI < 23 then BMI_cat = '19<=BMI<23';
 86         else                        BMI_cat = 'BMI Other ';
 87         
 88              if Age <32   then Age_cat = 'Age <32 ';
 89         else if Age >= 32 then Age_cat = 'Age >=32';
 90         
 91         ln_Progestrone = log(Progestrone_nmol_L);
 92         
 93         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       84:9    85:15   91:22   
 NOTE: Invalid numeric data, BMI='N.A' , at line 84 column 9.
 NOTE: Invalid numeric data, BMI='N.A' , at line 85 column 15.
 Preg_Group=Miscarriage Subject_number=LTNP Age=37 BMI=N.A AM=  PM=  Progestrone_nmol_L=8 Screen_Incl_Excl=Included Enrollment_Date=.
 GA_in_weeks=IUGS & YS GA=6 FP=No FH=  F_U_Date_16th_Wk=  F_U_Date_40th_Wk=  _16_week_miscarriage=Yes _40_week_miscarriage=N.A
 VAR18=Y Chromosomal_Aberration__Descript=Male profile with trisomy 16 VAR20=Y VAR21=Y VAR22=Y Completed_=Completed Remarks= 
 BMI_cat=BMI Other Age_cat=Age >=32 ln_Progestrone=2.0794415417 _ERROR_=1 _N_=34
 NOTE: Invalid numeric data, BMI='N.A' , at line 84 column 9.
 NOTE: Invalid numeric data, BMI='N.A' , at line 85 column 15.
 Preg_Group=Normal Subject_number=7 Age=24 BMI=N.A AM=N.A PM=16.4 Progestrone_nmol_L=51.47 Screen_Incl_Excl=Included
 Enrollment_Date=42857 GA_in_weeks=8+3 GA=8.43 FP=Yes FH=Yes F_U_Date_16th_Wk=42910 F_U_Date_40th_Wk=43077 _16_week_miscarriage=No
 _40_week_miscarriage=No VAR18=  Chromosomal_Aberration__Descript=  VAR20=Y VAR21=N VAR22=N Completed_=Completed Remarks= 
 BMI_cat=BMI Other Age_cat=Age <32 ln_Progestrone=3.9409991137 _ERROR_=1 _N_=47
 NOTE: There were 170 observations read from the data set RESEARCH.IMPORT.
 NOTE: The data set WORK.IMPORT has 114 observations and 27 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       cpu time            0.02 seconds
       
 
 94         
 95         title 'Cross tabulation on BMI and Age category groups';
 96         proc freq data=import; tables BMI_cat*Preg_Group/fisher; where BMI_cat ^= 'BMI Other '; run;
 
 NOTE: There were 61 observations read from the data set WORK.IMPORT.
       WHERE BMI_cat not = 'BMI Other ';
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.23 seconds
       cpu time            0.22 seconds
       
 
 97         proc freq data=import; tables Age_cat*Preg_Group/fisher;  run;
 
 NOTE: There were 114 observations read from the data set WORK.IMPORT.
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.18 seconds
       cpu time            0.16 seconds
       
 
 98         
 99         
 100        run;
 101        
 102        title 'Summary statistics';
 103        proc means data=import ndec=2 n mean std median p25 p75 min max; var Age BMI Progestrone_nmol_L;  run;
 ERROR: Variable BMI in list does not match type prescribed for this list.
 ERROR: Variable Progestrone_nmol_L in list does not match type prescribed for this list.
 
 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
       
 104        
 105        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 117

Appreciate your advice on how to resolve the error.

Thank you 🙂

Re: ERROR: Variable BMI in list does not match type prescribed for this list.

JeffMeyers — Thu, 11 Jun 2020 03:15:07 GMT

There are values in your BMI column in your Excel sheet that are causing it to import as a character. I can tell from the following notes in your data step log:
NOTE: Invalid numeric data, BMI='N.A' , at line 84 column 9.
NOTE: Invalid numeric data, BMI='N.A' , at line 85 column 15.

The following notes are also showing that BMI is currently a character variable:
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
84:9 85:15 91:22

You can either clean the character values out of the spreadsheet and try to import it, or create a new BMI variable that just uses the numeric values from BMI (e.g. BMI2=bmi*1). It looks like the same thing is happening for your progesterone _nmol_l variable.

Re: ERROR: Variable BMI in list does not match type prescribed for this list.

duckypooh — Thu, 11 Jun 2020 06:04:25 GMT

Hi! Thank you so much for pointing out the mistake! The issue was resolved when i created a new variable :)))

topic Re: ERROR: Variable BMI in list does not match type prescribed for this list. in New SAS User

ERROR: Variable BMI in list does not match type prescribed for this list.

Re: ERROR: Variable BMI in list does not match type prescribed for this list.

Re: ERROR: Variable BMI in list does not match type prescribed for this list.