Reading comma delimited file using SAS

Question

Raw file to be read using SAS has following records:

123,"Harold Wilson",Acct,01/15/1989,$78,123

128,"Julia Child",Food,08/29/1988,$89,123

007,"James Bond",Security,02/01/2000,$82,100

828,"Roger Doger",Acct,08/15/1999,$39,100

900,"Earl Davenport",Food,09/09/1989,$45,399

906,"James Swindler",Acct,12/21/1978,$78,200

SAS CODE :

options nocenter;

filename file1 'D:\files\SAS\raw-files\employee.csv';

data Employ;
infile file1 dsd;
input id       :       $3.
      name     :      $20.
      depart   :       $8.
      datehire : mmddyy10.  
      salary   :  dollar8.  
      ;
format datehire mmddyy10.
       salary    dollar8.;
run;

title 'Employee details';

proc print data=Employ;
run;

OUTPUT

Employee details

Obs id name depart datehire salary

1 123 Harold Wilson Acct 01/15/1989 $78

2 128 Julia Child Food 08/29/1988 $89

3 007 James Bond Security 02/01/2000 $82

4 828 Roger Doger Acct 08/15/1999 $39

5 900 Earl Davenport Food 09/09/1989 $45

6 906 James Swindler Acct 12/21/1978 $78

ISSUE :

How to get the salary in the same format as in the raw file as the salary part after the comma is ignored.

Answer 1

If you created this CSV file yourself, you should create it again. SAS sees the comma in the data value as a field separator (as would any other application).

If you cannot re-create the file, you can read the two columns as different text variables, concatenate them with a comma, and "read" the result using the INPUT function:

data Employ;
   infile file1 dsd truncover;
   input id       :       $3.
         name     :      $20.
         depart   :       $8.
         datehire : mmddyy10.
         salary_a :       $8.
         salary_b :       $8.
         ;
   salary = input( catx(',',salary_a,salary_b),dollar8.);
   drop salary_a salary_b;

   format datehire mmddyy10.
          salary    dollar8.;
run;

Notice the truncover option is added to your infile statement. This is important to allow SAS to properly handle the last variable (salary_b).

Answer 2

Following up on Bob's excellent answer above you could also do:

 data test;
  infile cards dsd truncover;
  input id name & : $20. depart : $12. datehire anydtdte10. salary comma8.;
  format id z3. datehire mmddyy10. salary dollar8.;
  cards;
  123,"Harold Wilson",Acct,01/15/1989,$78,123
  128,"Julia Child",Food,08/29/1988,$89,123
  007,"James Bond",Security,02/01/2000,$82,100
  828,"Roger Doger",Acct,08/15/1999,$39,100
  900,"Earl Davenport",Food,09/09/1989,$45,399
  906,"James Swindler",Acct,12/21/1978,$78,200
  ;
  run;

  proc print;
  run;

Answer 3

I got the solution to my problem that is to use ampersand(&) while reading salary variable instead of colon(:) modifier and without using truncover

data Employ;
    infile file1 dsd;
    input id       :       $3.
          name     :      $20.
          depart   :       $8.
          datehire : mmddyy10.  
          salary   &  dollar8.  
          ;
    format datehire mmddyy10.
           salary    dollar8.;
run;

ampersand(&) will continue to read the salary variable until two comma delimiters are encountered.Hence, the file can be read correctly.