importing a .dat file into sas

[CathyVI]

How do you import a .dat file ?

What will be the DBMS value ?

For example:

proc import data='C:\users\desktop\mysas\name.dat'

dbms= ?

out= test;

Note: You cannot open the file to check the content.

Thanks

[mkeintz]

I know what a .DTA file usually is (a data file produced by Stata).  But I am unaware of any likely convention for .DAT files.  Do you know what software produced your .DAT file?

[jklaverstijn]

A .dat extension is very generic. It tells us nothing about the way the file is constructed. Please elaborate on the contents. Having said that, this is very concerning:

Note: You cannot open the file to check the content

How can one possibly advise without checking the contents?

Regards,

-- Jan.

[hashman]

@CathyVI:

Do not use proc IMPORT. Use the DATA step: It has no objections to any extension and will read the file as long as it is readable. For example, I've created a test text file with 2 records, renamed it using the .dat extension, and, lo and behold:

1  filename in "C:\Users\sashole\Documents\test\test.dat" ;                                                                             
2                                                                                                                                       
3  data _null_ ;                                                                                                                        
4    infile in ;                                                                                                                        
5    input ;                                                                                                                            
6    put _infile_ ;                                                                                                                     
7  run ;                                                                                                                                
                                                                                                                                        
NOTE: The infile IN is:                                                                                                                 
      Filename=C:\Users\sashole\Documents\test\test.dat,                                                                                
      RECFM=V,LRECL=32767,File Size (bytes)=22,                                                                                         
      Last Modified=14Jan2020:17:23:56,                                                                                                 
      Create Time=14Jan2020:17:23:56                                                                                                    
                                                                                                                                        
1234567890                                                                                                                              
XYZABCQWER                                                                                                                              
NOTE: 2 records were read from the infile IN.   

Kind regards

Paul D.

[Reeza · Accepted solution]

---

@CathyVI wrote:

Note: You cannot open the file to check the content.

---

DAT files are just text files with data in them 90% of the time. 

Open the text file in an editor and examine the data and variables. 

Use the 'show invisible characters' feature in your text editor to see what the delimiter is, often it's a space, tab or even a comma. 

Then you can import it in SAS either using a custom data step or specifying the appropriate DBMS according to the delimiter.

If your file is some other format, there's almost no way for us to know what it is or how to handle it otherwise. 

---

@CathyVI wrote:

How do you import a .dat file ?

What will be the DBMS value ?

For example:

proc import data='C:\users\desktop\mysas\name.dat'

dbms= ?

out= test;

Note: You cannot open the file to check the content.

Thanks

---

[Kalai]

Hi,

Can you please try this code. 

PROC IMPORT DATAFILE = '/folders/myfolders/data/np_traffic.dat'
OUT = np
DBMS = DLM REPLACE;
DELIMITER="|";
RUN;

[carmenvila]

This works! Thank you.

[Kedarnath_M]

Proc Import datafile="/folders/myfolders/EPG1V2/data/np_traffic.dat"
dbms=dlm out=work.traffic2 replace;
guessingrows=max;
delimiter="|";
run;

There you go I actually managed to do it myself without looking at the solution 😁 (for the base SAS training).

Yeah as one other person said, you would have to open the file to find out what the delimiter is. For a CSV it would be a comma, but for a data file, you wouldn't know so you would have to check. In my example, the delimiter was a | so I used the delimiter= option to tell that to SAS.

I guess in your case you could try a guess and check method to see what the delimiter is until you get it right.

[Tom]

No need to guess. Just look at the file.

data _null_;
  infile "/folders/myfolders/EPG1V2/data/np_traffic.dat" obs=5 ;
  input;
  put _infile_;
run;

[Kedarnath_M]

Sweet, I downloaded the file and opened it with TextEdit, but this more convenient.

[Tom]

Also try the LIST statement.  Very useful when the file contains tabs (or other non-graphic characters) since SAS will then display the hexadecimal code for the characters in two lines aligned underneath the actual line of text.  It will also display the number of bytes in the line.

data _null_;
  infile "/folders/myfolders/EPG1V2/data/np_traffic.dat" obs=5 ;
  input;
  list;
run;

[subhash747]

use "dlm" as dbms value and add delimiter="?" step in code where "?" is the delimiter you need to define looking at the data in your .dat file.

[subhash747]

for example

proc import datafile="/home/data/xyz.dat" dbms= dlm
out=xyz_import replace;
guessingrows=max;
delimiter="|"
run;