BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CathyVI
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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


 

View solution in original post

12 REPLIES 12
mkeintz
PROC Star

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
jklaverstijn
Rhodochrosite | Level 12

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
Ammonite | Level 13

@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
Super User

@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
Calcite | Level 5

Hi,

Can you please try this code. 

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

 

carmenvila
Calcite | Level 5

This works! Thank you.

Kedarnath_M
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Calcite | Level 5

for example

 

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 45931 views
  • 16 likes
  • 10 in conversation