import .txt file in SAS

0
votes

I am trying to import text file in sas. Data as below: 

AccNumber            Name                     Date of Birth          Type                     City                     Score                
1211111111           Mmmmm Ggggg             01-Dec-1989              Base                     Nanded                   111                      
7222222222           Rannnn Sssss            14-Jan-1989              Silver                   mumbai                     222   

FILENAME REFFILE '/folders/myshortcuts/MyFolder/AccountChar.txt';
PROC IMPORT DATAFILE=REFFILE
DBMS=csv
OUT=WORK.IMPORT2;
GETNAMES=YES;
delimiter='09'x;
RUN;
PROC CONTENTS DATA=WORK.IMPORT2; RUN;

But, after import, I got a dataset with 107 columns and only Account number column is showing correct data.

Need help.

Log output:
NOTE: 296 records were read from the infile REFFILE.The minimum record length was 128.The maximum record length was 150. NOTE: The data set WORK.IMPORT5 has 296 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 296 rows created in WORK.IMPORT5 from REFFILE.

NOTE: WORK.IMPORT5 data set was successfully created. NOTE: The data set WORK.IMPORT5 has 296 observations and 1 variables. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.14 seconds cpu time 0.13 seconds PROC CONTENTS DATA=WORK.IMPORT5; RUN;

2
sas
You need to show what is actually in your text file. Run a simple data step to check. data _null_; infile '/folders/myshortcuts/MyFolder/AccountChar.txt' obs=2; input; list; run; – Tom
ouput: RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 1 AccountNumber Name Date of Birth Type 101 City FicoScore 150 2 9281332154 Mishti Gupta 01-Dec-1989 Base 101 Nashik 757 150 NOTE: 2 records were read from the infile '/folders/myshortcuts/MyFolder/AccountChar.txt'. – GNH
Change GUESSINGROWS to a larger number, i.e. 10000. PROC IMPORT is a guessing procedure if it doesn't work with the large number post the LOG to your question via an EDIT. – Reeza
Looks like your data file is NOT using either TABS or COMMA as delimiter, but instead just has spaces. Unless the values are in fixed columns the embedded spaces in fields like CITY will make the file unreadable. – Tom

2 Answers

1
votes

From the sample you posted and the comments it looks like your file is NOT a delimited file, but it does appear to have data in fixed column locations. Just figure out where on the line each column is placed and read it directly using a data step. Something like this:

data WORK.IMPORT2;
  infile '/folders/myshortcuts/MyFolder/AccountChar.txt' firstobs=2 truncover;
  input
    AccountNumber $ 1-25
    Name $ 26-50
    @51 Date_of_Birth date11.
    Type $ 74-98
    City $ 99-123
    Fica 124-130
  ;
  format date_of_birth date9. ;
run;
0
votes

You are providing a delimiter option '09'x (tab) which is ignored as your dbms is set to csv.

Try:

FILENAME REFFILE '/folders/myshortcuts/MyFolder/AccountChar.txt';
PROC IMPORT DATAFILE=REFFILE
DBMS=dlm  /* use delimiter option */
OUT=WORK.IMPORT2;
GETNAMES=YES;
delimiter='09'x;
RUN;
PROC CONTENTS DATA=WORK.IMPORT2; RUN;

For more info, see documentation