showing specific columns from loaded from CSV file in SAS

Question

i have a question to ask, i'm dealing with a small csv database where i need to perform some calculations with SAS, i have exported an excel file to CSV format and i want to load some columns in SAS to work with, the problem i have encountered is the order of column mismatch after loading: here is the code :

cars6.txt
AMC,Concord,22,2930,4099
AMC,Concord,22,2930,4099
AMC,Pacer,17,3350,4749
AMC,Spirit,22,2640,3799
Buick,Century,20,3250,4816
Buick,Electra,15,4080,7827 

code to output data:

DATA cars6;
  INFILE "/folders/myfolders/hbv1/cars6.txt" delimiter=',';
  INPUT  make $ model $ mpg $ weight price;
RUN;

TITLE "cars5 data";
PROC PRINT DATA=cars5(OBS=5);
RUN;

but i want to show only the columns: Make , weight, price ? so how to print selected columns ? and how to do that if i have named columns (the example differs from this one only by column names 'variables' at the beginning) but i have tried to call the columns y name , it printed them but with bad data ( the sas is taking ordered column data and ignoring the column data based on column name :

input make $ model $ price $; 

thank You.

Answer 1

If you are writing you own program to read a CSV file then you probably want to use the DSD and FIRSTOBS=2 options on the INFILE statement. This will treat missing values properly and skip the line with the variable names. You also probably want to add the TRUNCOVER option to properly handle lines that have only some of the columns. It is worth the extra work to properly define your variables by including LENGTH or ATTRIB statements. Otherwise SAS will have to guess whether you want numeric or character variables and how long to make the character variables from the way that you first reference them.

DATA cars6;
  INFILE "/folders/myfolders/hbv1/cars6.txt" DSD DLM=',' FIRSTOBS=2 TRUNCOVER;
  LENGTH make model $20 mpg weight price 8 ;
  INPUT  make model mpg weight price;
RUN;

But your program will need to know the order of the variables in the file. If your data files are inconsistent then you can try using PROC IMPORT to read the CSV file. It can take the names from the first row and make an educated guess at what the variable types are.

proc import datafile='/folders/myfolders/hbv1/cars6.txt' out=car6 replace dbms=dlm ;
   delimiter=',';
   getnames=yes;
run;

When using the data from the SAS dataset you have created you can use the SAS language to select the columns of interest. Syntax will depend on the procedure you are using. So for PROC PRINT use the VAR statement.

proc print data=car6 ;
  var price make model;
run;

And for PROC FREQ use the TABLES statement.

proc freq data=car6;
  tables make model;
run;

Answer 2

Consider using proc import and select columns as needed in print. Proc import can handle comma-separated files saved as either .txt or .csv. Below is demonstration of either text file type:

%Let fpath = /folders/myfolders/hbv1;

** READING IN TXT;
proc import
    datafile = "&fpath/cars6.txt"
    out = Cars6
    dbms = csv replace; 
run;

** READING IN CSV;
proc import
    datafile = "&fpath/cars6.csv"
    out = Cars6
    dbms = csv replace;
run;

title "cars6 data";
proc print data=cars1(obs=5);
    var make model price;
run;

Alternatively, you can drop variables and re-order needed columns for report with retain:

data CarsReport;
    retain make model price;
    set Cars6;
    keep make model price;
run;

title "cars6 data";
proc print data=CarsReport(obs=5);    
run;

Answer 3

Try the VAR statement in PROC PRINT.

DATA cars6;
  INFILE "/folders/myfolders/hbv1/cars6.txt" delimiter=',' firstobs=2;
  INPUT  make $ model $ mpg $ weight price;
RUN;

proc print data=cars6 noobs;
var make weight price;
run;