Write and run SAS programs in your web browser

how to create aditional column

Reply
Contributor
Posts: 26

how to create aditional column

 

 i was using car dataset and trying to create a column MSRP inr with the help of MSRP column.

column was created but no values are showing in column MSRP inr..... help

CODE :-

libname jonty '/folders/myfolders/jonty';

FILENAME REFFILE '/folders/myfolders/jonty/CARS.csv';

PROC IMPORT DATAFILE=REFFILE replace
DBMS=CSV
OUT=jonty.racing;
GETNAMES=YES;
guessingrows=100;
RUN;

data jonty.car_Dodge_2;
set jonty.racing;
where make = "Dodge";
MSRP_inr = MSRP *65;
run;

 

 output :-

 
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
61
62 libname jonty '/folders/myfolders/jonty';
NOTE: Libref JONTY was successfully assigned as follows:
Engine: V9
Physical Name: /folders/myfolders/jonty
63
64 FILENAME REFFILE '/folders/myfolders/jonty/CARS.csv';
65
66 PROC IMPORT DATAFILE=REFFILE replace
67 DBMS=CSV
68 OUT=jonty.racing;
69 GETNAMES=YES;
70 guessingrows=100;
71 RUN;
 
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
72 /**********************************************************************
73 * PRODUCT: SAS
74 * VERSION: 9.4
75 * CREATOR: External File Interface
76 * DATE: 21JUN17
77 * DESC: Generated SAS Datastep Code
78 * TEMPLATE SOURCE: (None Specified.)
79 ***********************************************************************/
80 data JONTY.RACING ;
81 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
82 infile REFFILE delimiter = ',' MISSOVER DSD firstobs=2 ;
83 informat Make $9. ;
84 informat Model $32. ;
85 informat Type $6. ;
86 informat Origin $6. ;
87 informat DriveTrain $5. ;
88 informat MSRP $9. ;
89 informat Invoice $9. ;
90 informat EngineSize best32. ;
91 informat Cylinders best32. ;
92 informat Horsepower best32. ;
93 informat MPG_City best32. ;
94 informat MPG_Highway best32. ;
95 informat Weight best32. ;
96 informat Wheelbase best32. ;
97 informat Length best32. ;
98 format Make $9. ;
99 format Model $32. ;
100 format Type $6. ;
101 format Origin $6. ;
102 format DriveTrain $5. ;
103 format MSRP $9. ;
104 format Invoice $9. ;
105 format EngineSize best12. ;
106 format Cylinders best12. ;
107 format Horsepower best12. ;
108 format MPG_City best12. ;
109 format MPG_Highway best12. ;
110 format Weight best12. ;
111 format Wheelbase best12. ;
112 format Length best12. ;
113 input
114 Make $
115 Model $
116 Type $
117 Origin $
118 DriveTrain $
119 MSRP $
120 Invoice $
121 EngineSize
122 Cylinders
123 Horsepower
124 MPG_City
125 MPG_Highway
126 Weight
127 Wheelbase
128 Length
129 ;
130 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
131 run;
 
NOTE: The infile REFFILE is:
Filename=/folders/myfolders/jonty/CARS.csv,
Owner Name=root,Group Name=vboxsf,
Access Permission=-rwxrwx---,
Last Modified=21Jun2017:12:49:15,
File Size (bytes)=37716
 
NOTE: 428 records were read from the infile REFFILE.
The minimum record length was 68.
The maximum record length was 111.
NOTE: The data set JONTY.RACING has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
 
 
428 rows created in JONTY.RACING from REFFILE.
 
 
 
NOTE: JONTY.RACING data set was successfully created.
NOTE: The data set JONTY.RACING has 428 observations and 15 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.24 seconds
cpu time 0.15 seconds
 
 
132
133 data jonty.car_Dodge_2;
134 set jonty.racing;
135 where make = "Dodge";
136 MSRP_inr = MSRP *65;
137 run;
 
NOTE: Character values have been converted to numeric values at the places given by: (Line)Smiley SadColumn).
136:12
NOTE: Invalid numeric data, MSRP='$32,235' , at line 136 column 12.
Make=Dodge Model=Durango SLT Type=SUV Origin=USA DriveTrain=All MSRP=$32,235 Invoice=$29,472 EngineSize=4.7 Cylinders=8
Horsepower=230 MPG_City=15 MPG_Highway=21 Weight=4987 Wheelbase=119 Length=201 MSRP_inr=. _ERROR_=1 _N_=1
NOTE: Invalid numeric data, MSRP='$13,670' , at line 136 column 12.
Make=Dodge Model=Neon SE 4dr Type=Sedan Origin=USA DriveTrain=Front MSRP=$13,670 Invoice=$12,849 EngineSize=2 Cylinders=4
Horsepower=132 MPG_City=29 MPG_Highway=36 Weight=2581 Wheelbase=105 Length=174 MSRP_inr=. _ERROR_=1 _N_=2
NOTE: Invalid numeric data, MSRP='$15,040' , at line 136 column 12.
Make=Dodge Model=Neon SXT 4dr Type=Sedan Origin=USA DriveTrain=Front MSRP=$15,040 Invoice=$14,086 EngineSize=2 Cylinders=4
Horsepower=132 MPG_City=29 MPG_Highway=36 Weight=2626 Wheelbase=105 Length=174 MSRP_inr=. _ERROR_=1 _N_=3
NOTE: Invalid numeric data, MSRP='$22,035' , at line 136 column 12.
Make=Dodge Model=Intrepid SE 4dr Type=Sedan Origin=USA DriveTrain=Front MSRP=$22,035 Invoice=$20,502 EngineSize=2.7 Cylinders=6
Horsepower=200 MPG_City=21 MPG_Highway=29 Weight=3469 Wheelbase=113 Length=204 MSRP_inr=. _ERROR_=1 _N_=4
NOTE: Invalid numeric data, MSRP='$18,820' , at line 136 column 12.
Make=Dodge Model=Stratus SXT 4dr Type=Sedan Origin=USA DriveTrain=Front MSRP=$18,820 Invoice=$17,512 EngineSize=2.4 Cylinders=4
Horsepower=150 MPG_City=21 MPG_Highway=28 Weight=3182 Wheelbase=108 Length=191 MSRP_inr=. _ERROR_=1 _N_=5
NOTE: Invalid numeric data, MSRP='$20,220' , at line 136 column 12.
Make=Dodge Model=Stratus SE 4dr Type=Sedan Origin=USA DriveTrain=Front MSRP=$20,220 Invoice=$18,821 EngineSize=2.4 Cylinders=4
Horsepower=150 MPG_City=21 MPG_Highway=28 Weight=3175 Wheelbase=108 Length=191 MSRP_inr=. _ERROR_=1 _N_=6
NOTE: Invalid numeric data, MSRP='$24,885' , at line 136 column 12.
Make=Dodge Model=Intrepid ES 4dr Type=Sedan Origin=USA DriveTrain=Front MSRP=$24,885 Invoice=$23,058 EngineSize=3.5 Cylinders=6
Horsepower=232 MPG_City=18 MPG_Highway=27 Weight=3487 Wheelbase=113 Length=204 MSRP_inr=. _ERROR_=1 _N_=7
NOTE: Invalid numeric data, MSRP='$21,795' , at line 136 column 12.
Make=Dodge Model=Caravan SE Type=Sedan Origin=USA DriveTrain=Front MSRP=$21,795 Invoice=$20,508 EngineSize=2.4 Cylinders=4
Horsepower=150 MPG_City=20 MPG_Highway=26 Weight=3862 Wheelbase=113 Length=189 MSRP_inr=. _ERROR_=1 _N_=8
NOTE: Invalid numeric data, MSRP='$32,660' , at line 136 column 12.
Make=Dodge Model=Grand Caravan SXT Type=Sedan Origin=USA DriveTrain=All MSRP=$32,660 Invoice=$29,812 EngineSize=3.8 Cylinders=6
Horsepower=215 MPG_City=18 MPG_Highway=25 Weight=4440 Wheelbase=119 Length=201 MSRP_inr=. _ERROR_=1 _N_=9
NOTE: Invalid numeric data, MSRP='$81,795' , at line 136 column 12.
Make=Dodge Model=Viper SRT-10 convertible 2dr Type=Sports Origin=USA DriveTrain=Rear MSRP=$81,795 Invoice=$74,451 EngineSize=8.3
Cylinders=10 Horsepower=500 MPG_City=12 MPG_Highway=20 Weight=3410 Wheelbase=99 Length=176 MSRP_inr=. _ERROR_=1 _N_=10
NOTE: Invalid numeric data, MSRP='$17,630' , at line 136 column 12.
Make=Dodge Model=Dakota Regular Cab Type=Truck Origin=USA DriveTrain=Rear MSRP=$17,630 Invoice=$16,264 EngineSize=3.7 Cylinders=6
Horsepower=210 MPG_City=16 MPG_Highway=22 Weight=3714 Wheelbase=112 Length=193 MSRP_inr=. _ERROR_=1 _N_=11
NOTE: Invalid numeric data, MSRP='$20,300' , at line 136 column 12.
Make=Dodge Model=Dakota Club Cab Type=Truck Origin=USA DriveTrain=Rear MSRP=$20,300 Invoice=$18,670 EngineSize=3.7 Cylinders=6
Horsepower=210 MPG_City=16 MPG_Highway=22 Weight=3829 Wheelbase=131 Length=219 MSRP_inr=. _ERROR_=1 _N_=12
NOTE: Invalid numeric data, MSRP='$20,215' , at line 136 column 12.
Make=Dodge Model=Ram 1500 Regular Cab ST Type=Truck Origin=USA DriveTrain=Rear MSRP=$20,215 Invoice=$18,076 EngineSize=3.7
Cylinders=6 Horsepower=215 MPG_City=16 MPG_Highway=21 Weight=4542 Wheelbase=121 Length=208 MSRP_inr=. _ERROR_=1 _N_=13
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)Smiley SadColumn).
13 at 136:17
NOTE: There were 13 observations read from the data set JONTY.RACING.
WHERE make='Dodge';
NOTE: The data set JONTY.CAR_DODGE_2 has 13 observations and 16 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
 
Super User
Posts: 7,762

Re: how to create aditional column

Because of the dollar sign, MSRP was imported by proc import (keep in mind that it only makes GUESSES about your data, and computers are dumb!) as character.

A string $12345 does not constitute valid numeric data, so you get ERRORs where SAS attempts an automatic conversion.

Take the data step code generated by the proc import from the log. Assign an informat of dollar9. to MSRP (log line 88), and remove the dollar sign after MSRP in the input statement (log line 119). You need to remove the log line numbers before you run the data step code, of course.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 1 reply
  • 91 views
  • 1 like
  • 2 in conversation