topic Re: Proc import txt or excel files to make sure i have the leading zeros in the sas data tables in SAS Programming

Proc import txt or excel files to make sure i have the leading zeros in the sas data tables

kmin87 — Thu, 03 Jun 2021 19:47:35 GMT

Below is sample code I am using. Information was replaced with an arbitrary information just for privacy reasons.

when I import the text file that has rows of numbers that have leading zeros in sas, I get the information with the leading zeros dropped in the master datatable in sas. how do I make sure I maintain the leading zeros when I import the data

proc import datafile="\\somedrive\test.txt"

out=master

dbms=dlm

replace

;

run;

the test.txt file has these social security numbers listed in rows

000111111

222222222

044444444

Re: Proc import txt or excel files to make sure i have the leading zeros in the sas data tables

Tom — Thu, 03 Jun 2021 20:08:29 GMT

Don't use PROC IMPORT.

You could try modifying the code that PROC IMPORT generates (but it generates really ugly SAS code). It is usually better (and often faster) to just write your own code.

data master;
  infile "\\somedrive\test.txt" dsd truncover firstobs=1;
  input ssn $9. ;
run;

Re: Proc import txt or excel files to make sure i have the leading zeros in the sas data tables

LeonidBatkhan — Thu, 03 Jun 2021 20:35:02 GMT

Hi kmin87,

Yes, you can either make them type of character (e.g. using $9. informat as @Tom suggested), or if you keep it numeric, you can also define FORMAT SSN z9 (which will show leading zero even when the number is stored without them).

Another way to deal with SSNs is to store them as NUMERIC values and apply FORMAT SSN11. (in the latter case, formatted values will show as NNN-NN-NNNN, that is with hyphens).

Hope this helps.

Re: Proc import txt or excel files to make sure i have the leading zeros in the sas data tables

Ksharp — Fri, 04 Jun 2021 12:41:33 GMT

Or try option guessingrows= :

proc import datafile="\\somedrive\test.txt"
out=master
dbms=tab
replace
;
guessingrows=max;
run;

Re: Proc import txt or excel files to make sure i have the leading zeros in the sas data tables

Tom — Fri, 04 Jun 2021 12:44:25 GMT

@Ksharp wrote:
Or try option guessingrows= :

proc import datafile="\\somedrive\test.txt"
out=master
dbms=tab
replace
;
guessingrows=max;
run;

That will not make any difference for this problem. PROC IMPORT will decide the variable is numeric if the only values it sees in the column are digit strings. It is NOT smart enough to attach the Z format to the numeric variable.