topic Re: Importing a text file with semicolons in SAS Procedures

Importing a text file with semicolons

jcis7 — Wed, 24 Jun 2015 18:06:20 GMT

Hello, any help you can give is much appreciated!

I am trying to import a .txt file which has fields separated by semicolons.

When I use proc import the following proc import statement, the first column in the SAS table called 'FCode' which is a 14 digit code imports with a BEST format and is numeric and imports as a bunch of numbers with E in it.

But, I need the filed to be character and have leading zeroes (it has leading zeroes in the file). How do you do that? My code so far below -- Thanks!

proc IMPORT DATAFILE= "E:\enroll.txt"

DBMS=dlm

out=ethk1415

delimiter=';';

getnames=yes;

run;

Re: Importing a text file with semicolons

Reeza — Wed, 24 Jun 2015 18:10:18 GMT

Look at your log. Take that code, which is a data step and modify it, since the data step gives you more control over your input data types.

Re: Importing a text file with semicolons

dcruik — Wed, 24 Jun 2015 18:30:39 GMT

As Reeza said, use a data step import.

data ethk1415;

infile "E:\enroll.txt"

dlm=";" missover firstobs=2;

informat var1 informat1. ....... varn informatn.;

format var1 format1. ....... varn formatn.;

input var1 ....... varn;

run;

Re: Importing a text file with semicolons

jcis7 — Wed, 24 Jun 2015 22:36:59 GMT

Thank you both! Appreciate your help!!

Q1)How do you know how long a field is in a .txt file?

Also, I tried the code below but some of the names (starting from name3) were cutoff so I'm thinking the 'input @' number is wrong.

Q2) But, how do you know where to start the next input # if you don't know how long the fields are? I tried importing into a MS Excel file and then imported into SAS. The field lengths were the following in this order in the .txt file.

code 14

name 50

name1 50

name2 17

total 12

data enrl;

infile "E:\enroll.txt"

dlm=";" missover firstobs=2;

informat code $14.

name $50.

name2 $50.

name3 $17.

total 12.;

input @1 code

15 name

65 name2

115 name3

132 total;

run

;

Re: Importing a text file with semicolons

Tom — Thu, 25 Jun 2015 01:40:57 GMT

If nobody has given you any documentation on the fields in the file then you don't know how long the fields are. But you could just make them longer than they need to be.

If you are reading a delimited file then you do NOT need to know where to start the next number. The delimiters mark the transition between fields.

Do NOT use an INFORMAT statement to do the function of a LENGTH statement. There is no need to assign an informat like $14 to a character variable.

Why are your name fields different lengths? Don't they contain the same type of information? Why are they named NAME NAME1 NAME2 instead of NAME1 NAME2 NAME3?

data enrl;

infile "E:\enroll.txt" dsd dlm=";" TRUNCOVER firstobs=2;

length code $14 name1-name3 $50 total 8;

input code -- total;

run;

Re: Importing a text file with semicolons

Ksharp — Thu, 25 Jun 2015 13:15:23 GMT

Try another option guessingrow=32767

Re: Importing a text file with semicolons

jcis7 — Thu, 25 Jun 2015 16:07:01 GMT

Appreciate your help everyone!!