How to import csv file where first row is variable names?

[mikepark]

I have a csv file with the top row being variable names. 

There are about 20 columns and I only want to keep 5.

How do I import this file?

When I have imported it so far, it ends up listing 3 of the columns I want to keep as missing. 

Then, the first column has a the correct header with the wrong data. Amy advice is appreciated.

Thanks!

[Kurt_Bremser]

Please post the first three lines of your file, using the {i} button.

[mikepark]

data fuel;
	length MfrName $ 50 Carline $ 100;
	keep MfrName Carline EngDispl Cyl CombFE;
	infile 'C:\myfolders\Lesson10_2016EPAfuelEconomy.csv' dsd firstobs=2;
	input MfrName $ Carline $ EngDispl Cyl CombFE;
run;

Here is my code. 

[Kurt_Bremser]

I wanted you to post the first three lines of the csv file.

[mikepark]

Model Year	MfrName	Division	Carline	Verify Mfr Cd	Index (Model Type Index)	EngDispl	Cyl	Transmission	City FE (Guide) - Conventional Fuel	Hwy FE (Guide) - Conventional Fuel	CombFE	City Unadj FE - Conventional Fuel	Hwy Unadj FE - Conventional Fuel	Comb Unadj FE - Conventional Fuel	City Unrd Adj FE - Conventional Fuel	Hwy Unrd Adj FE - Conventional Fuel	Comb Unrd Adj FE - Conventional Fuel	Guzzler? 	Air Aspir Method	Air Aspiration Method Desc	Trans	Trans Desc	Trans, Other	# Gears	Lockup Torque Converter	Trans Creeper Gear	Drive Sys	Drive Desc	Max Ethanol % - Gasoline	Max Biodiesel %	Range1 - Model Type Driving Range - Conventional Fuel	Fuel Usage  - Conventional Fuel	Fuel Usage Desc - Conventional Fuel	Fuel Unit - Conventional Fuel	Fuel Unit Desc - Conventional Fuel	Gas Guzzler Exempt (Where Truck = 1975 NHTSA truck definition)	Gas Guzzler Exempt Desc (Where Truck = 1975 NHTSA truck definition)	2Dr Pass Vol	2Dr Lugg Vol	4Dr Pass Vol	4Dr Lugg Vol	Htchbk Pass Vol	Htchbk Lugg Vol	Annual Fuel1 Cost - Conventional Fuel	EPA Calculated Annual Fuel Cost - Conventional Fuel -----  Annual fuel cost error. Please revise Verify. 	City2 FE (Guide) - Alternative Fuel	Hwy2 Fuel FE (Guide) - Alternative Fuel	Comb2 Fuel FE (Guide) - Alternative Fuel	City2 Unadj FE - Alternative Fuel	Hwy2 Unadj FE - Alternative Fuel	Comb2 Unadj FE - Alternative Fuel	City2 Unrd Adj FE - Alternative Fuel	Hwy2 Unrd Adj FE - Alternative Fuel	Cmb2 Unrd Adj FE - Alternative Fuel	 Range2 - Alt Fuel Model Typ Driving Range - Alternative Fuel	 Fuel2 Usage - Alternative Fuel	 Fuel2 Usage Desc - Alternative Fuel	Fuel2 Unit - Alternative Fuel	Fuel2 Unit Desc - Alternative Fuel	Fuel2 Annual Fuel Cost - Alternative Fuel	City CO2 Rounded Adjusted - Fuel2	Hwy CO2 Rounded Adjusted - Fuel2	Comb CO2 Rounded Adjusted - Fuel2	Fuel2 EPA Calculated Annual Fuel Cost - Alternative Fuel	Descriptor - Model Type (40 Char or less)	Intake Valves Per Cyl	Exhaust Valves Per Cyl	Carline Class	Carline Class Desc	Car/Truck Category - Cash for Clunkers Bill.	Calc Approach Desc	Release Date	EPA FE Label Dataset ID		Unique Label?	Label Recalc?	Relabel	Relabel Desc	Suppressed?	Police/Emerg?	Comments - Mfr Eng Cnfg	Cyl Deact?	Cyl Deact Desc	Var Valve Timing?	Var Valve Timing Desc	Var Valve Lift?	Var Valve Lift Desc	Energy Storage Device Desc	Energy Storage Device,If Other	# Batteries	Battery Type Desc	Battery Type, If Other	Total Voltage for Battery Pack(s)	Batt Energy Capacity (Amp-hrs)	Batt Specific Energy (Watt-hr/kg)	Batt Charger Type Desc	Comments	# Capacitors	Regen Braking Type Desc	Regen Braking Type, If Other	Regen Braking Wheels Source (Front, Rear, Both)	Driver Cntrl Regen Braking?	Fuel Cell Desc	Usable H2 Fill Capacity (kg)	Fuel Cell Onboard H2 Capacity (kg)	HEV-EV Comments	# Drive Motor Gen	Motor Gen Type Desc	Motor Gen Type, If Other	Rated Motor Gen Power (kW)	Fuel Metering Type 1 Desc	Fuel Metering Type 2 Desc	Fuel Metering Sys Cd	Fuel Metering Sys Desc	Fuel Cell Vehicle (Y or N)	Off Board Charge Capable (Y or N)	Camless Valvetrain (Y or N)	Oil Viscosity	Stop/Start System (Engine Management System) Code	Stop/Start System (Engine Management System)  Description	Model Type Desc (MFR entered)	Charge Depleting Calc Appr Code (PHEV only)	Charge Depleting Calc Appr Desc (PHEV only)	Charge Sustaining Calc Appr Code (PHEV only)	Charge Sustaining Calc Appr Desc (PHEV only)	EPA Calculated Annual Fuel Cost	EPA Calculated Gas Guzzler MPG	MFR Calculated Gas Guzzler MPG 	EPA Calculated Gas Guzzler Indicator (Y or N)		FE Rating (1-10 rating on Label)	GHG Rating (1-10 rating on Label)	GHG 1-10 rating on Ethanol (EPA Determined)	#1 Smog Rating Test Group	#1 Mfr Smog Rating (Mfr Smog 1-10 Rating on Label for Test Group 1)	#1 EPA Smog Rating (EPA Smog 1-10 Rating on Label for Test Group 1)	SmartWay1 (EPA-derived SmartWay rating for Test Group 1)	#2 Smog Rating Test Group	#2 Mfr Smog Rating (Mfr Smog 1-10 Rating on Label for Test Group 2)	#2 EPA Smog Rating (EPA Smog 1-10 Rating on Label for Test Group 2)	SmartWay2 (EPA-derived SmartWay rating for Test Group 2)	#3 Smog Rating Test Group	#3 Mfr Smog Rating (Mfr Smog 1-10 Rating on Label for Test Group 3)	#3 EPA Smog Rating (EPA Smog 1-10 Rating on Label for Test Group 3)	SmartWay3 (EPA-derived SmartWay rating for Test Group 3)	#4 Smog Rating Test Group	#4 Mfr Smog Rating (Mfr Smog 1-10 Rating on Label for Test Group 4)	#4 EPA Smog Rating (EPA Smog 1-10 Rating on Label for Test Group 4)	SmartWay4 (EPA-derived SmartWay rating for Test Group 4)	$ You Save over 5 years (amount saved in fuel costs over 5 years - on label) 	$ You Spend over 5 years (increased amount spent in fuel costs over 5 years - on label) 	City CO2 Rounded Adjusted	Hwy CO2 Rounded Adjusted	Comb CO2 Rounded Adjusted (as shown on FE Label)	CO2-PHEV Composite Rounded Adjusted Combined CO2	240V Charge Time at 240 volts (hours)	120V Charge time at 120 Volts (hours)	PHEV Total Driving Range (rounded to nearest 10 miles)DISTANCE	City PHEV Composite MPGe	Hwy PHEV Composite MPGe	Comb PHEV Composite MPGe
2016	FCA US LLC	ALFA ROMEO	4C	CRX	124	1.8	4	Auto(AM6)	24	34	28	28.7	45.7	34.4702	24.2492	34.3275	27.9406		TC	Turbocharged	AM	Automated Manual		6	Y	N	R	2-Wheel Drive, Rear	10			GP	Gasoline (Premium Unleaded Recommended)	MPG	miles per gallon	N	Not exempt							1750	1750																				SIDI; 	2	2	1	Two Seaters	car	Vehicle Specific 5-cycle label	8/17/2015	18712		N	N			N	N	Direct gas injected	N		Y	Cam Phasers	N																											GDI	Spark Ignition Direct Injection			N	5W-30	N	No								34.7			7	7		GCRXV01.85P0	5															250		365	259	317							
2016	aston martin	Aston Martin Lagonda Ltd	V12 Vantage S	ASX	8	6	12	Auto(AM7)	12	18	14	14.7893	25.3011	18.1901	12.0366	18.3154	14.2321	G	NA	Naturally Aspirated	AM	Automated Manual		7	N	N	R	2-Wheel Drive, Rear	10			GP	Gasoline (Premium Unleaded Recommended)	MPG	miles per gallon	N	Not exempt							3550	3550																					2	2	1	Two Seaters	car	Derived 5-cycle label	6/15/2015	18924		N	N			N	N	V12 Bosch HP engine config - carline 281 (V12 Vantage S)	N		Y	Inlet and Exhaust Cam Phasing	N																											MFI	Multipoint/sequential fuel injection	N		N	0W40	N	No	V12 Vantage S							18.3			2	2		GASXV06.0VHB	5																8750	742	488	628							

Sorry about that.  It comes out strange on here. They year 2016 is under the year column in my file. Things got moved around when I copied and pasted. 

Added: I just noticed some commas in the header

[Kurt_Bremser]

This is NOT a csv file AT ALL. It's a tab separated file, so you need to treat it as such. The extension should be .tsv. And it's a Windows text file.

Your infile statement therefore needs to be

infile 'C:\myfolders\Lesson10_2016EPAfuelEconomy.csv' dlm='09'x termstr=CRLF dsd firstobs=2;

And your input statement

input dummy MfrName :$20. dummyc $ Carline :$20. dummyc $ dummyc $ EngDispl Cyl dummyc $ dummy dummy CombFE;

followed by

drop dummy dummyc;

After copying your file to my server (binary mode), I ran this code

data want;
infile '$HOME/sascommunity/ahobby9.tsv' dlm='09'x termstr=CRLF dsd firstobs=2;
input dummy MfrName :$20. dummyc $ Carline :$20. dummyc $ dummyc $ EngDispl Cyl dummyc $ dummy dummy CombFE;
drop dummy dummyc;
run;

proc print data=want noobs;
run;

and got this result:

                                  Eng            Comb
  MfrName       Carline          Displ    Cyl     FE

FCA US LLC      4C                1.8       4     28 
aston martin    V12 Vantage S     6.0      12     14 

[ballardw]

Also it may help to show the code you have used so far.

With delimited files generally you will need to read each "column" up to the last one you want and then drop the ones you don't want.

Here is a skeleton of a program to read unwanted values into a single variable and then drop it.

data raw;
   infile myfile delimiter=',' MISSOVER DSD lrecl=32767 firstobs=2 ;
   informat dropvar              $1. ;
   informat CaseNumber           $9. ;
   informat SiteID               best32. ;
   informat ProgramID            best32. ;
   informat ProgramStartDate     yymmdd10. ;
   informat EndDate              yymmdd10. ;
   informat TerminationReasonID  $6. ;
   informat ReasonForDismissalID $6. ;
   informat ReasonForDismissal   $60. ;  

   format ProgramStartDate mmddyy10. ;
   format EndDate          mmddyy10. ;
   input
   dropvar
   CaseNumber
   dropvar 
   SiteID
   ProgramID
   ProgramStartDate
   EndDate
   TerminationReasonID
   ReasonForDismissalID
   dropvar 
   dropvar
   dropvar
   dropvar 
   ReasonForDismissal $
   ;
   drop dropvar;
run;