BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
duckypooh
Calcite | Level 5

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
JeffMeyers
Barite | Level 11
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.

View solution in original post

2 REPLIES 2
JeffMeyers
Barite | Level 11
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.
duckypooh
Calcite | Level 5

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 774 views
  • 0 likes
  • 2 in conversation