topic Re: Import .txt tab delimited file in SAS Programming

Import .txt tab delimited file

jcis7 — Fri, 25 Oct 2013 23:40:49 GMT

Good Afternoon,

I'm importing a .txt file (Tab delimited).

In the .txt file there's a variable that is 14 digits long & some start with zeros.

Original Number in Text File:

ID TxtFileVble

1 01611430000000

2 19734376023758

Once imported into SAS using File, Import Wizard, SAS converts the variable into a 'BEST12.' numeric format and the leading zero drops:

ID ImportedVble

1 1.61143E12

2 1.9734376E13

I need to do 3 things:

FIRST: I need to convert ImportedVble from the numberic BEST format into a character format with a leading zero only where it was missing so it looks like this:

ID ZeroAddedVble

1 01611430000000

2 19734376023758

SECOND: I need to Parse it out (using Substr?) 3 new variables:

1st new variable (1st and 2nd digits of 'OldVble'

ID NewVbleA

1 01

2 19

2nd new variable (3rd digit through 7th digit of 'OldVble')

ID NewVbleB

1 03456

2 73437

3rd new vble (8th digit through 14th digit of 'OldVble')

ID NewVbleC

1 0000000

2 6023758

What's the most efficient way to do this?

I tried this but it doesn't work:

data rename;

set fileA;

format NewVbleA $2. NewVbleB $5. NewVbleC $7.

char_ImportedVble=strip(put(ImportedVble, 13.));

dr0p ImportedVble;

rename char_ImportedVble=ImportedVble;

NewVbleA=substr(ImportedVble, 1, 2)

NewVbleB=substr(ImportedVble, 3,7)

NewVlbeC=substr(ImportedVble, 8,7);

run;

THIRD: In the text file one variable is a name of an institution but when I used proc import, it cutoff all of the variable and only retained the first letter.

How do you make sure SAS doesn't cut the information off? Is it better to just import the .txt file into an Excel file and then import into SAS?

Any help you can give is much appreciated!

Re: Import .txt tab delimited file

Tom — Sat, 26 Oct 2013 00:10:49 GMT

You can get control by just writing the code to read the file yourself.

The code to read a tab delimited file is easy to write yourself if you know the variable names. (just grab the first line of the file and paste it into the program editor!).

data want ;

infile 'myfile.txt' dlm='09'x dsd truncover lrecl=1000000;

length ID 8 TxtFileVble $14 ;

input id txtfilevble ;

run;

If you are using SAS display manager you can use the IMPORT wizard (or call PROC IMPORT) and then recall the data step that it generated and fix it.

If you have a number and you want to convert it to a text string with leading zero then use the PUT function.

newvar = put(oldvar,Z14.);

If you are happy to leave it as a number and just want it to LOOK like it has leading zeros then just attach a format to it.

format oldvar Z14. ;

There are a number of issues with your data step.

1) You are using a FORMAT statement where you clearly want a LENGTH statement. Format does NOT define the length of a variable, other than in the normal way that SAS will guess what type of variable you want based on how you first reference it.

2) Just use the PUT() statement to generate the string variable. Use Z format to add the leading zeros. You said it was length 14 , but use format of only 13.

3) Typo with a zero in place of the letter 0.

4) SUBSTR() syntax is substr(STRING,beginning [,length] ).

5) You need to reference the new variable using the name it has in this data step, the RENAME does not take effect until the record is written out.

data rename;

set fileA;

length char_ImportedVble $14 NewVbleA $2 NewVbleB $5 NewVbleC $7 ;

char_ImportedVble=put(ImportedVble,Z14.);

drop ImportedVble;

rename char_ImportedVble=ImportedVble;

NewVbleA=substr(char_ImportedVble, 1,2)

NewVbleB=substr(char_ImportedVble, 3,5)

NewVlbeC=substr(char_ImportedVble, 8);

run;

Truncation could be caused by LRECL setting being too short. Or if you used PROC IMPORT then SAS might not have examined all of the file and so set the variable length too short to store the full name. You can try the GUESSINGROWS setting.

Re: Import .txt tab delimited file

Jagadishkatam — Sat, 26 Oct 2013 00:18:15 GMT

Please try the below code,

i used infile statement to import the tab delimited .txt file. also with regard to the data truncation, while importing in the input statement use the name and give the maximum length possible something like name :$20.

filename test "~path\test.txt";

data have;

infile test dlm='09'x missover;

input iD TxtFileVble :$20.;

NewVbleA=substr(TxtFileVble,1,2);

NewVbleB=substr(TxtFileVble,3,7);

NewVbleC=substr(TxtFileVble,8,7);

run;

Thanks,

Jagadish