topic Re: Reading in a non-delimited file into SAS in SAS Enterprise Guide

Reading in a non-delimited file into SAS

Sean_OConnor — Wed, 02 Feb 2022 11:58:00 GMT

Good Morning,

I have a file which is not delimited so it's difficult to judge when one column ends and other begins. However, I have metadata around the maximum size the columns will be and whether they should be numeric or alphanumeric. Below is a snippet of the information.

Can anyone recommend an efficient way to read these and create a SAS dataset, please?

VAR Name	Max length	Type
Var1	9	Alphanumeric
Var2	13	Numeric
Var3	3	Numeric
Var4	3	Numeric
Var5	8	Alphanumeric
Var6	8	Alphanumeric
Var7	1	Alphanumeric
Var8	4	Alphanumeric
Var9	13	Numeric

Re: Reading in a non-delimited file into SAS

Kurt_Bremser — Wed, 02 Feb 2022 12:02:15 GMT

Please open your file with a text editor(!) and copy/paste a few lines into a window opened with this button:

Re: Reading in a non-delimited file into SAS

Sean_OConnor — Wed, 02 Feb 2022 12:06:55 GMT

Hi Kurt. Unfortunately I can't do this as it isn't publicly available data. I'm wondering if an infile, informat, input statement work?

Re: Reading in a non-delimited file into SAS

Kurt_Bremser — Wed, 02 Feb 2022 12:11:28 GMT

Without seeing an example for the data, I can give you exactly zero help. I can have no clue if you have some kind of column separator, or fixed width columns, or any other thing that can help in locating column boundaries.

Your request is like requesting a diagnosis from a doctor who is made deliberately deaf and blind and is not allowed to touch you.

Please post the example as requested, and overwrite sensitive values with X's or 0's (one character for one character, and leave everything else - special characters! - untouched).

Re: Reading in a non-delimited file into SAS

Sajid01 — Wed, 02 Feb 2022 13:26:02 GMT

Hello @Sean_OConnor
This should not be difficult. Something which one does come across.I used the approach as shown in the example below.

Hope this should solve your issue.

data test;
length line $ 
256;
input line $;
datalines;
AB1234567123456789ABCDEFGH
;;
run;
data test2;
set test;
var1=substr(line,1,9);
var2=substr(line,10,9);
var3=substr(line,19,3);
var4=substr(line,22);
run;

Re: Reading in a non-delimited file into SAS

ballardw — Wed, 02 Feb 2022 15:29:02 GMT

If your source is text and there actually is no delimiter then likely the data is fixed column. This documentation is the basic approach for that type of data:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/lestmtsref/n13ejk9swz5vrbn0z34iazfrp0wp.htm

Specify the columns to read (hint: Var1 is columns 1 to 9, Var2 is 10 to 22 if your description is accurate and the data is fixed column).

Caution: 13 digits for numbers sounds likely to be odd. If these are things like account numbers or identifiers that are not to be used in numeric calculations likely they should be character. If they have leading zeroes that you want to see then they really should be character. Unfortunately many folks don't make a difference in their documentation and will say anything that contains only digits is "numeric".

Note: if you have date, time or date time values you will want to be very specific about the informat used to read them to get actual SAS date, time or datetime values and assign an appropriate format for human legibility.

Re: Reading in a non-delimited file into SAS

FreelanceReinh — Wed, 02 Feb 2022 17:06:08 GMT

Hello @Sean_OConnor,

Assuming that your file has fixed field widths, here's a simple code example using formatted input (which I prefer to column input) and five variables:

/* Create test data for demonstration */

filename ft15f001 temp;
parmcards;
B.1.1.52911,222,333.00-12987SOMETEXT
B.1.351 S49,999.95    17 101shorter
;

/* Read test data */

data want;
infile ft15f001 truncover;
input var1 $9.
      var2 comma13.
      var3 3.
      var4 3.
      var5 $upcase8.;
run;

The TRUNCOVER option helps to avoid problems if the record length may vary (as in the example). The length specifications (9, 13, 3, ...) in each informat are important as they determine the columns to be read for each variable (i.e., columns 1 - 9 for VAR1, 10 - 22 for VAR2, ...). For the character variables they also define the lengths (if not defined otherwise, e.g., by a LENGTH statement). Use character informats for character variables and numeric informats for numeric variables. (Note that the SAS variable type can differ from the raw data type, e.g., the numeric DATE9. informat can read an "alphanumeric" field with data like 01Jan2022 and vice versa, as mentioned by ballardw.) Of course, your INFILE statement will refer to a different file name or to a physical path.