topic Re: importing a .dat file into sas in SAS Programming

importing a .dat file into sas

CathyVI — Tue, 14 Jan 2020 21:13:31 GMT

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

Re: importing a .dat file into sas

mkeintz — Tue, 14 Jan 2020 21:51:09 GMT

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?

Re: importing a .dat file into sas

jklaverstijn — Tue, 14 Jan 2020 22:13:03 GMT

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.

Re: importing a .dat file into sas

hashman — Tue, 14 Jan 2020 22:33:37 GMT

@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.

Re: importing a .dat file into sas

Reeza — Tue, 14 Jan 2020 23:43:01 GMT

@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

Re: importing a .dat file into sas

Kalai — Fri, 17 Jul 2020 08:17:52 GMT

Hi,

Can you please try this code.

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

Re: importing a .dat file into sas

carmenvila — Fri, 18 Sep 2020 21:05:00 GMT

This works! Thank you.

Re: importing a .dat file into sas

Kedarnath_M — Sat, 26 Dec 2020 13:28:34 GMT

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.

Re: importing a .dat file into sas

Tom — Sat, 26 Dec 2020 15:18:47 GMT

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;

Re: importing a .dat file into sas

Kedarnath_M — Sat, 26 Dec 2020 15:31:11 GMT

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

Re: importing a .dat file into sas

Tom — Sat, 26 Dec 2020 16:32:29 GMT

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;

Re: importing a .dat file into sas

subhash747 — Fri, 08 Apr 2022 11:11:08 GMT

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.

Re: importing a .dat file into sas

subhash747 — Fri, 08 Apr 2022 11:12:51 GMT

for example

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