topic Re: Create break lines from text file; Importing text files with no delimitators in SAS Programming

Create break lines from text file; Importing text files with no delimitators

fastandcurious — Tue, 21 Feb 2023 20:54:34 GMT

Hello everyone,

Shot in the dark but is anyone is familiar with reading in text files that have no delimitators, or know how to specify different delimitators? I'll take any advice. I have to import a file that is in .dat format. Unfortunately there are no delimitators but I know what the length of each column should be.

I currently have to use excel text import wizard and manually create "break lines" based off a format key, and I was hoping there was a way to do it in SAS.

Example:

The format key provides the length of each variable in order to specify proper line breaks.

For example, if var1 is a length of 4, I want to tell SAS delineate var1 as a length of 4, var2 is a length of 8, etc.

Re: Create break lines from text file; Importing text files with no delimitators

Tom — Tue, 21 Feb 2023 22:08:33 GMT

This is basic SAS programming 101. How to read in data.

You can use column mode input, where you list the column number (or range of columns) after each variable.

data want;
  infile 'myfile.txt' truncover;
  input var1 $ 1-4 var2 $ 5-12 ;
run;

Or you can use formatted mode input, where you list the informat to use after each variable.

data want;
  infile 'myfile.txt' truncover;
  input var1 $4. var2 $8. ;
run;

Re: Create break lines from text file; Importing text files with no delimitators

ballardw — Tue, 21 Feb 2023 22:23:26 GMT

This is such a common activity that given a good description of the data, (should include which variables are actually numeric, dates, times or datetime values and the expected layout of the values, such as dates in yyyy/mm/dd or ddMONyyyy or currency values) that I use a spreadsheet to write Informat and input statements.

Note: there is no standard file format for the .DAT extension. For instance, one version of DAT means "digital audio tape" format and was a moderately common sound file once upon a time. I have worked with DAT extension files that are fixed column, delimited and named (every value is shown as variablename=value like name='John' age=23 ), not to mention some in the appearance of reports that require lots of parsing.

Caution: do not save that source text file when opened by a spreadsheet, the values may change on you.

Re: Create break lines from text file; Importing text files with no delimitators

fastandcurious — Wed, 22 Feb 2023 14:34:50 GMT

Thank you SO much for this. I ended up using the formatted mode input and it worked:
data want;
infile 'myfile.txt' truncover;
input var1 $4. var2 $8. ;
run;
I'm still a beginner and I appreciate this because sometimes I get projects where I know there's a simpler solution in SAS but I hit a wall for a variety of reasons. Somehow I never reviewed or had to use infile/input.
I was able to import nearly 300 variables using this method and it saved me so much time and stress while also feeling very accomplished.
For anyone following, I also took advice from Ballardw and a spreadsheet to write the informats and it was helpful.

Re: Create break lines from text file; Importing text files with no delimitators

Tom — Wed, 22 Feb 2023 15:43:56 GMT

@fastandcurious wrote:
Thank you SO much for this. I ended up using the formatted mode input and it worked:
data want;
infile 'myfile.txt' truncover;
input var1 $4. var2 $8. ;
run;
I'm still a beginner and I appreciate this because sometimes I get projects where I know there's a simpler solution in SAS but I hit a wall for a variety of reasons. Somehow I never reviewed or had to use infile/input.
I was able to import nearly 300 variables using this method and it saved me so much time and stress while also feeling very accomplished.
For anyone following, I also took advice from Ballardw and a spreadsheet to write the informats and it was helpful.

No need using a spreadsheet to point and click your way through manipulating text. SAS data steps are very good at that.

So if you have metadata like this:

data vars ;
  input name :$32. type :$4. width ;
cards;
var1 num 4
var2 char 8
;

You can use it to write the input statement.

data _null_;
  set vars end=eof;
  if _n_=1 then put 'input ' @;
  length informat $32 ;
  if type='char' then informat=cats('$',width,'.');
  else informat=cats(width,'.');
  put name informat @;
  if eof then put ';' ;
run;

Which will print this line into the log

input var1 4. var2 $8. ;

You could stop there and just copy and paste the line into your program, just like I copied it here. Or you could automate even more by writing the line into a file instead and then using %INCLUDE to add the line into your program.

filename code temp;
data _null_;
  file code;
  set vars end=eof;
  if _n_=1 then put 'input ' @;
  length informat $32 ;
  if type='char' then informat=cats('$',width,'.');
  else informat=cats(width,'.');
  put name informat @;
  if eof then put ';' ;
run;
data want;
  infile 'myfile.txt' truncover;
%include code / source2;
run;

You can make it more useful by including INFORMAT and FORMAT fields in the metadata and use those to help generate the code to read the actual data.

Re: Create break lines from text file; Importing text files with no delimitators

fastandcurious — Wed, 22 Feb 2023 19:33:16 GMT

Thank you so much for this, this is so helpful and informative! I'm going to look into applying this to the dataset over the next week or so. I appreciate your insight 🙂