Creating new Numerical Variables

[Jweinstein2001]

Im trying to create two new numeric variables. My code is giving me the error saying the data is not able to be found within the lines, and for the other variable, nothing appears at all. I don't believe I'm doing anything wrong but obviously am.

[ballardw]

General best practice for this forum when discussing any code error:  Copy the text of the code and any notes, warnings and/or errors from the LOG and then paste the result in a text box opened on the forum using the </> icon that appears above the message window.

The text box will prevent the main message window from reformatting text helping keep the diagnostic characters that SAS supplies with many errors in the correct position.

So rerun you code if needed then copy / paste the text from the log with the error.

[Jweinstein2001]

I kept getting marked as spam when I did this, 

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
       38:1     RC
 68         
 69         options MSGLEVEL=I;
 70         libname Ec351 "/home/u59307513/Ec351";
 NOTE: Libref EC351 was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u59307513/Ec351
 71         proc import datafile='/home/u59307513/Ec351/CarPrice Regression Dataset Ec351.xls' dbms=xls out=EC351.Project1 replace;
 72         getnames=yes;
 73         run;
 
 NOTE: VARCHAR data type is not supported by the V9 engine. Variable Transmission Type has been converted to CHAR data type.
 NOTE: The import data set has 11914 observations and 7 variables.
 NOTE: EC351.PROJECT1 data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.03 seconds
       user cpu time       0.02 seconds
       system cpu time     0.00 seconds
       memory              3242.00k
       OS Memory           36836.00k
       Timestamp           12/05/2022 09:37:00 PM
       Step Count                        721  Switch Count  2
       Page Faults                       0
       Page Reclaims                     669
       Page Swaps                        0
       Voluntary Context Switches        54
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           1544
       
 
 74         
 75         
 76         data ec351.projectsorted;
 77         set ec351.project1;
 78         options validvarname=any;
 79         if year < 2017 then delete;
 80         run;
 
 NOTE: There were 11914 observations read from the data set EC351.PROJECT1.
 NOTE: The data set EC351.PROJECTSORTED has 1668 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              1605.68k
       OS Memory           35252.00k
       Timestamp           12/05/2022 09:37:00 PM
       Step Count                        722  Switch Count  2
       Page Faults                       0
       Page Reclaims                     254
       Page Swaps                        0
       Voluntary Context Switches        63
       Involuntary Context Switches      0
       Block Input Operations            1568
       Block Output Operations           264
       
 
 81         
 82         data ec351.projectuseabledata ;
 83         set ec351.projectsorted;
 84         if "Transmission Type"n = 'MANUAL' then Trans = 0;
 85         else Trans = 1 ;
 86         if "Number_of_doors" = 2 then doors = 0;
 87         else doors = 1;
 88         drop year;
 89         AvgMPG = ('City_MPG'+'Highway_MPG')/2;
 90         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       86:4    89:11   89:22   
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=27495 Trans=0 doors=1 AvgMPG=.
 _ERROR_=1 _N_=1
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=24995 Trans=0 doors=1 AvgMPG=.
 _ERROR_=1 _N_=2
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=28195 Trans=0 doors=1 AvgMPG=.
 _ERROR_=1 _N_=3
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=31 city mpg=21 MSRP=46450 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=4
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=32 city mpg=21 MSRP=49050 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=5
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=32 city mpg=21 MSRP=51050 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=6
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=32 city mpg=21 MSRP=44450 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=7
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=34 city mpg=23 MSRP=38950 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=8
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=35 city mpg=24 MSRP=33150 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=9
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=33 city mpg=24 MSRP=35150 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=10
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=33 city mpg=23 MSRP=40950 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=11
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=295 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=28 city mpg=18 MSRP=29905 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=12
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=295 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=28 city mpg=18 MSRP=31785 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=13
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=26685 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=14
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=22490 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=15
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=25690 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=16
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=295 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=28 city mpg=18 MSRP=30900 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=17
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=27795 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=18
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=21995 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=19
 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.
 NOTE: Invalid numeric data, 'City_MPG' , at line 89 column 11.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 89 column 22.
 WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=24490 Trans=1 doors=1 AvgMPG=.
 _ERROR_=1 _N_=20
 NOTE: Missing values were generated as a result of performing an operation on missing values.
       Each place is given by: (Number of times) at (Line):(Column).
       1668 at 89:21   
 NOTE: There were 1668 observations read from the data set EC351.PROJECTSORTED.
 NOTE: The data set EC351.PROJECTUSEABLEDATA has 1668 observations and 9 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              1173.59k
       OS Memory           34740.00k
       Timestamp           12/05/2022 09:37:00 PM
       Step Count                        723  Switch Count  1
       Page Faults                       0
       Page Reclaims                     99
       Page Swaps                        0
       Voluntary Context Switches        50
       Involuntary Context Switches      0
       Block Input Operations            288
       Block Output Operations           536
       
 
 91         
 92         proc print data= ec351.projectuseabledata;
 93         run;
 
 NOTE: There were 1668 observations read from the data set EC351.PROJECTUSEABLEDATA.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           2.46 seconds
       user cpu time       2.46 seconds
       system cpu time     0.00 seconds
       memory              1481.00k
       OS Memory           34480.00k
       Timestamp           12/05/2022 09:37:02 PM
       Step Count                        724  Switch Count  0
       Page Faults                       0
       Page Reclaims                     82
       Page Swaps                        0
       Voluntary Context Switches        13
       Involuntary Context Switches      9
       Block Input Operations            544
       Block Output Operations           1000
       
 
 94         
 95         
 96         
 97         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 107        

[Tom]

The log is showing you where the error is.  So for example this line:

 NOTE: Invalid numeric data, 'Number_of_doors' , at line 86 column 4.

Is talking about this line in the log.

 86         if "Number_of_doors" = 2 then doors = 0;

Where you are comparing the string 'Number_of_doors' to the integer 2.  To compare a string to a number SAS tried to convert the string into a number, but that string is not something that SAS can convert into a number.

I suspect you wanted to test if the variable named Number_of_doors we equal to 2 or not.

if Number_of_doors = 2 then doors = 0;

Fix the other similar issues and see if that makes the code work.

[Jweinstein2001]

Now it is saying the variables are uninitialized.

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
       38:1     RC
 68         
 69         options MSGLEVEL=I;
 70         libname Ec351 "/home/u59307513/Ec351";
 NOTE: Libref EC351 was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u59307513/Ec351
 71         proc import datafile='/home/u59307513/Ec351/CarPrice Regression Dataset Ec351.xls' dbms=xls out=EC351.Project1 replace;
 72         getnames=yes;
 73         run;
 
 NOTE: VARCHAR data type is not supported by the V9 engine. Variable Transmission Type has been converted to CHAR data type.
 NOTE: The import data set has 11914 observations and 7 variables.
 NOTE: EC351.PROJECT1 data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.04 seconds
       user cpu time       0.02 seconds
       system cpu time     0.00 seconds
       memory              3242.71k
       OS Memory           37092.00k
       Timestamp           12/05/2022 09:56:14 PM
       Step Count                        730  Switch Count  2
       Page Faults                       0
       Page Reclaims                     669
       Page Swaps                        0
       Voluntary Context Switches        51
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           1544
       
 
 74         
 75         
 76         data ec351.projectsorted;
 77         set ec351.project1;
 78         options validvarname=any;
 79         if year < 2017 then delete;
 80         run;
 
 NOTE: There were 11914 observations read from the data set EC351.PROJECT1.
 NOTE: The data set EC351.PROJECTSORTED has 1668 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              1604.59k
       OS Memory           35508.00k
       Timestamp           12/05/2022 09:56:14 PM
       Step Count                        731  Switch Count  2
       Page Faults                       0
       Page Reclaims                     263
       Page Swaps                        0
       Voluntary Context Switches        64
       Involuntary Context Switches      0
       Block Input Operations            1568
       Block Output Operations           264
       
 
 81         
 82         data ec351.projectuseabledata ;
 83         set ec351.projectsorted;
 84         if "Transmission Type"n = 'MANUAL' then Trans = 0;
 85         else Trans = 1 ;
 86         if Number_of_doors = 2 then doors = 0;
 87         else doors = 1;
 88         drop year;
 89         AvgMPG = (City_MPG+Highway_MPG)/2;
 90         run;
 
 NOTE: Variable Number_of_doors is uninitialized.
 NOTE: Variable Highway_MPG is uninitialized.
 NOTE: Variable City_MPG is uninitialized.
 NOTE: Missing values were generated as a result of performing an operation on missing values.
       Each place is given by: (Number of times) at (Line):(Column).
       1668 at 89:19   
 NOTE: There were 1668 observations read from the data set EC351.PROJECTSORTED.
 NOTE: The data set EC351.PROJECTUSEABLEDATA has 1668 observations and 12 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1172.15k
       OS Memory           34996.00k
       Timestamp           12/05/2022 09:56:14 PM
       Step Count                        732  Switch Count  1
       Page Faults                       0
       Page Reclaims                     94
       Page Swaps                        0
       Voluntary Context Switches        48
       Involuntary Context Switches      0
       Block Input Operations            288
       Block Output Operations           520
       
 
 91         
 92         proc print data= ec351.projectuseabledata;
 93         run;
 
 NOTE: There were 1668 observations read from the data set EC351.PROJECTUSEABLEDATA.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           3.36 seconds
       user cpu time       3.36 seconds
       system cpu time     0.00 seconds
       memory              1491.96k
       OS Memory           34736.00k
       Timestamp           12/05/2022 09:56:17 PM
       Step Count                        733  Switch Count  0
       Page Faults                       0
       Page Reclaims                     95
       Page Swaps                        0
       Voluntary Context Switches        15
       Involuntary Context Switches      3
       Block Input Operations            544
       Block Output Operations           1248
       
 
 94         
 95         
 96         
 97         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 107        

[Tom]

You have to use the actual names of your variables.  If you use PROC IMPORT then you need to actually look at the result and determine what names it guessed to use for the file you gave it.  Run PROC CONTENTS on the dataset to see the actual names that PROC IMPORT created.  

[Astounding]

Most of the time, variable names do not belong in quotes.  You had to do this for Transmission Type, to be able to specify "Transmission Type"n so the variable name could contain a blank.  But in the other cases, the quotes around variable names should be removed.

That might take care of all the errors ... try it and we'll find out.

[Jweinstein2001]

options MSGLEVEL=I;
libname Ec351 "/home/u59307513/Ec351";
proc import datafile='/home/u59307513/Ec351/CarPrice Regression Dataset Ec351.xls' dbms=xls out=EC351.Project1 replace;
getnames=yes;
run;


data ec351.projectsorted;
set ec351.project1;
options validvarname=any;
if year < 2017 then delete;
run;

data ec351.projectuseabledata ; 
set ec351.projectsorted;
if "Transmission Type"n = 'MANUAL' then Trans = 0;
else Trans = 1 ;
drop year; 
AvgMPG = (("City_MPG"+"Highway_MPG")/2);
run;

proc print data= ec351.projectuseabledata;
run;

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
       38:1     RC
 68         
 69         options MSGLEVEL=I;
 70         libname Ec351 "/home/u59307513/Ec351";
 NOTE: Libref EC351 was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/u59307513/Ec351
 71         proc import datafile='/home/u59307513/Ec351/CarPrice Regression Dataset Ec351.xls' dbms=xls out=EC351.Project1 replace;
 72         getnames=yes;
 73         run;
 
 NOTE: VARCHAR data type is not supported by the V9 engine. Variable Transmission Type has been converted to CHAR data type.
 NOTE: The import data set has 11914 observations and 7 variables.
 NOTE: EC351.PROJECT1 data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.03 seconds
       user cpu time       0.02 seconds
       system cpu time     0.00 seconds
       memory              3242.43k
       OS Memory           36580.00k
       Timestamp           12/05/2022 08:57:59 PM
       Step Count                        640  Switch Count  2
       Page Faults                       0
       Page Reclaims                     670
       Page Swaps                        0
       Voluntary Context Switches        50
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           1544
       
 
 74         
 75         
 76         data ec351.projectsorted;
 77         set ec351.project1;
 78         options validvarname=any;
 79         if year < 2017 then delete;
 80         run;
 
 NOTE: There were 11914 observations read from the data set EC351.PROJECT1.
 NOTE: The data set EC351.PROJECTSORTED has 1668 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              1491.40k
       OS Memory           34996.00k
       Timestamp           12/05/2022 08:57:59 PM
       Step Count                        641  Switch Count  2
       Page Faults                       0
       Page Reclaims                     262
       Page Swaps                        0
       Voluntary Context Switches        63
       Involuntary Context Switches      0
       Block Input Operations            1568
       Block Output Operations           264
       
 
 81         
 82         data ec351.projectuseabledata ;
 83         set ec351.projectsorted;
 84         if "Transmission Type"n = 'MANUAL' then Trans = 0;
 85         else Trans = 1 ;
 86         drop year;
 87         AvgMPG = (("City_MPG"+"Highway_MPG")/2);
 88         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       87:12   87:23   
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=27495 Trans=0 AvgMPG=. _ERROR_=1
 _N_=1
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=24995 Trans=0 AvgMPG=. _ERROR_=1
 _N_=2
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=28195 Trans=0 AvgMPG=. _ERROR_=1
 _N_=3
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=31 city mpg=21 MSRP=46450 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=4
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=32 city mpg=21 MSRP=49050 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=5
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=32 city mpg=21 MSRP=51050 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=6
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=335 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=32 city mpg=21 MSRP=44450 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=7
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=34 city mpg=23 MSRP=38950 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=8
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=35 city mpg=24 MSRP=33150 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=9
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=33 city mpg=24 MSRP=35150 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=10
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=248 Transmission Type=AUTOMATIC Number of Doors=2 highway MPG=33 city mpg=23 MSRP=40950 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=11
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=295 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=28 city mpg=18 MSRP=29905 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=12
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=295 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=28 city mpg=18 MSRP=31785 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=13
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=26685 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=14
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=22490 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=15
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=25690 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=16
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=295 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=28 city mpg=18 MSRP=30900 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=17
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=27795 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=18
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=21995 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=19
 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
 Year=2017 Engine HP=184 Transmission Type=AUTOMATIC Number of Doors=4 highway MPG=36 city mpg=23 MSRP=24490 Trans=1 AvgMPG=.
 _ERROR_=1 _N_=20
 NOTE: Missing values were generated as a result of performing an operation on missing values.
       Each place is given by: (Number of times) at (Line):(Column).
       1668 at 87:22   
 NOTE: There were 1668 observations read from the data set EC351.PROJECTSORTED.
 NOTE: The data set EC351.PROJECTUSEABLEDATA has 1668 observations and 8 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1174.59k
       OS Memory           34484.00k
       Timestamp           12/05/2022 08:57:59 PM
       Step Count                        642  Switch Count  1
       Page Faults                       0
       Page Reclaims                     99
       Page Swaps                        0
       Voluntary Context Switches        51
       Involuntary Context Switches      0
       Block Input Operations            288
       Block Output Operations           280
       
 
 89         
 90         proc print data= ec351.projectuseabledata;
 91         run;
 
 NOTE: There were 1668 observations read from the data set EC351.PROJECTUSEABLEDATA.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           2.30 seconds
       user cpu time       2.29 seconds
       system cpu time     0.00 seconds
       memory              1327.03k
       OS Memory           34480.00k
       Timestamp           12/05/2022 08:58:01 PM
       Step Count                        643  Switch Count  0
       Page Faults                       0
       Page Reclaims                     147
       Page Swaps                        0
       Voluntary Context Switches        20
       Involuntary Context Switches      16
       Block Input Operations            288
       Block Output Operations           904
       
 
 92         
 93         
 94         
 95         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 105        

The variable AVGMPG comes up as a ".", and I was wondering why I'm getting an error when its trying to read the values of both MPG variables.

[Kurt_Bremser]

 87         AvgMPG = (("City_MPG"+"Highway_MPG")/2);

"City_MPG" is a character value which cannot be converted to numeric. If City_MPG is in fact a variable name, omit the quotes, likewise for Highway_MPG.

[ballardw · Accepted solution]

 NOTE: Invalid numeric data, 'City_MPG' , at line 87 column 12.
 NOTE: Invalid numeric data, 'Highway_MPG' , at line 87 column 23.
 Year=2017 Engine HP=160 Transmission Type=MANUAL Number of Doors=2 highway MPG=35 city mpg=26 MSRP=27495 Trans=0 AvgMPG=. _ERROR_=1
 _N_=1

Looks like the variable names are 'highway MPG'n and 'city mpg'n not City_mpg or Highway_MPG. (and 'Transmission type'n and 'Number of doors'n)

Note: not a good idea to place system options in a data step. When the option goes into effect in relation to the given data step is problematic at best.

If if the Validvarname=any is in effect when the Proc import runs then the names are 'highway MPG'n and 'city mpg'n.

If you want the Highway_mpg and City_mpg then set option Validvarname=V7; before executing the proc import.

Or save the XLSX file as CSV and write a data step to read with the desired variable name and properties.

[Jweinstein2001]

Thank you very very much

[Kurt_Bremser]

I combined your threads dealing with the same issue.