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

I am attempting to import a pipe-delimited text file. I want all the fields to appear as text regardless of what transformations may occur later. For some reason, SAS decides that some fields are datetime and formats them accordingly. Is there a workaround with PROC IMPORT? If I use the infile statement, how do I get SAS to account for varying field lengths so they are not all cut off at 8 characters (I prefer not to specify lengths individually).

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

This is not a bug but the design of proc import.

If you need more control I would recommend running proc import. Then look in the LOG and you will find  datastep code
SAS generated to read the file. Copy the code from the log to the editor and modify the INFORMAT statements to character informats which would be $XX. where XX represents the number of characters in the field. Remove the FORMAT statements as basically not much need with character. And modify the input statment to have a $ after each variable.

If the data actually are dates or datetimes and you manipulate them at all you would probably be better off using the SAS date / datetime though.

View solution in original post

8 REPLIES 8
ballardw
Super User

This is not a bug but the design of proc import.

If you need more control I would recommend running proc import. Then look in the LOG and you will find  datastep code
SAS generated to read the file. Copy the code from the log to the editor and modify the INFORMAT statements to character informats which would be $XX. where XX represents the number of characters in the field. Remove the FORMAT statements as basically not much need with character. And modify the input statment to have a $ after each variable.

If the data actually are dates or datetimes and you manipulate them at all you would probably be better off using the SAS date / datetime though.

ArtC
Rhodochrosite | Level 12

When reading delimeted files, PROC IMPORT writes a DATA step, though somewhat less sophisticated than the one written by DATA _NULL_ below.  If you are running IMPORT from the Display Manager you can recover this program without copying from the LOG by using the RECALL command (under RUN) after executing the IMPORT .  This can save a bit of editing of extraneous characters that will be a part of the LOG.

Doug
Calcite | Level 5

Thanks Art - that is what I ended up doing and it worked well.

data_null__
Jade | Level 19

Here is an example that creates variables based on the field names of a pipe delimited file where the first row are field names.  The data is transposed into a model data set in this case with all character variables.  The model data is used to drive the data step to read in the data fields beginnin in row 2.

filename FT15F001 temp;
parmcards;
Name|Sex|Age|Height|Weight
Alfred|M|
14|69|112.5
Alice|F|
13|56.5|84
Barbara|F|
13|65.3|98
Carol|F|
14|62.8|102.5
Henry|M|
14|63.5|102.5
James|M|
12|57.3|83
Jane|F|
12|59.8|84.5
Janet|F|
15|62.5|112.5
Jeffrey|M|
13|62.5|84
John|M|
12|59|99.5
Joyce|F|
11|51.3|50.5
Judy|F|
14|64.3|90
Louise|F|
12|56.3|77
Mary|F|
15|66.5|112
Philip|M|
16|72|150
Robert|M|
12|64.8|128
Ronald|M|
15|67|133
Thomas|M|
11|57.5|85
William|M|
15|66.5|112
;;;;
   run;
data fields;
   infile FT15F001 obs=1 dsd dlm='|';
  
length name $32 dummy $128;
  
retain dummy ' ';
  
input name @@;
   run;
proc transpose out=model(drop=_name_);
   var dummy;
   id name;
   run;
data class;
   if 0 then set model;
   infile FT15F001 firstobs=2 dsd dlm='|';
  
input (_all_) (:);
   run;
proc print;
  
run;
Ksharp
Super User

One way is considering a loose length for all the variables .

data x;

infile 'xxxx';

input (var1 - var100) (: $200.);

....

Ksharp

Peter_C
Rhodochrosite | Level 12

that's a good time to use the compress=yes option

It is like having VARCHAR() for all fields instead of CHAR()

agoldma
Pyrite | Level 9

If you want PROC IMPORT to have an option to import all fields as character, please vote:

PROC IMPORT -- all fields as character

agoldma
Pyrite | Level 9

If you want PROC IMPORT to have an option to import all fields as character, please vote:

PROC IMPORT -- all fields as character

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1794 views
  • 1 like
  • 7 in conversation