BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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.

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 5444 views
  • 3 likes
  • 3 in conversation