BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Reeza
Super User

Hmm...there are ways around that, but you have 100 files that don't have the same structure? The name isn't available anywhere else?

tropical_surfer
Fluorite | Level 6

Each file has different data, with different variables/variable names. The last variable name for every file is always missing (ie VAR#). The raw text files have the correct variable name, just like for the other variables that import properly. The variable is named "Description" in the raw input below. Notice there is no delimiter at the end of the line, I'm guessing thats why it doesn't read the name in, although later rows are the same and read in correctly.

 

 

 

 

EFF_START_DATE|EFF_END_DATE|CODE|DESCRIPTION
9/01/2001 12:00:00 AM|12/31/9999 12:00:00 AM|AAA|EXAMPLE
9/01/2001 12:00:00 AM|12/31/9999 12:00:00 AM|BBB|EXAMPLE
9/01/2001 12:00:00 AM|12/31/9999 12:00:00 AM|CCC|EXAMPLE

Tom
Super User Tom
Super User

There must be something in the file.  Use a data step to read the first few lines and see what it is.

data _null_;
   infile 'myfile' obs=3 ;
   input;
   list;
run;

You example data works fine with PROC IMPORT.

 

56 options generic;
57 filename tst temp;
58 data _null_;
59 file tst ;
60 input ;
61 put _infile_;
62 cards;
 
NOTE: The file TST is:
(system-specific pathname),
(system-specific file attributes)
 
NOTE: 4 records were written to the file (system-specific pathname).
The minimum record length was 80.
The maximum record length was 80.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
 
67 ;
 
68
69 proc import datafile=tst out=tst replace
70 dbms=dlm
71 ;
72 delimiter='|';
73 run;
 
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
74 /**********************************************************************
75 * PRODUCT: SAS
76 * VERSION: 9.4
77 * CREATOR: External File Interface
78 * DATE: 30MAR16
79 * DESC: Generated SAS Datastep Code
80 * TEMPLATE SOURCE: (None Specified.)
81 ***********************************************************************/
82 data WORK.TST ;
83 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
84 infile TST delimiter = '|' MISSOVER DSD firstobs=2 ;
85 informat EFF_START_DATE anydtdtm40. ;
86 informat EFF_END_DATE anydtdtm40. ;
87 informat CODE $3. ;
88 informat DESCRIPTION $7. ;
89 format EFF_START_DATE datetime. ;
90 format EFF_END_DATE datetime. ;
91 format CODE $3. ;
92 format DESCRIPTION $7. ;
93 input
94 EFF_START_DATE
95 EFF_END_DATE
96 CODE $
97 DESCRIPTION $
98 ;
99 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
100 run;
 
NOTE: The infile TST is:
(system-specific pathname),
(system-specific file attributes)
 
NOTE: 3 records were read from the infile (system-specific pathname).
The minimum record length was 80.
The maximum record length was 80.
NOTE: The data set WORK.TST has 3 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
 
 
3 rows created in WORK.TST from TST.
 
 
 
NOTE: WORK.TST data set was successfully created.
NOTE: The data set WORK.TST has 3 observations and 4 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.08 seconds
cpu time 0.04 seconds
 
Reeza
Super User
No, I wouldn't expect a delimiter at end.

Review file as Tom indicates. You may also want to view in editor that shows invisibles and see if anything shows up.
tropical_surfer
Fluorite | Level 6

If you paste the sample data above into a blank notepad and save it as a .txt you can replicate the issue II'm having. This makes me doubt its the file formatting and I'm sort of at a loss for ideas.

Tom
Super User Tom
Super User

I can replicate the error by appending a tab or other invalid character to the end of the header line.

 

filename tst temp;
data _null_;
  file tst ;
  input ;
  _infile_=trim(_infile_);
  put _infile_ '09'x;
cards;
EFF_START_DATE|EFF_END_DATE|CODE|DESCRIPTION
9/01/2001 12:00:00 AM|12/31/9999 12:00:00 AM|AAA|EXAMPLE
9/01/2001 12:00:00 AM|12/31/9999 12:00:00 AM|BBB|EXAMPLE
9/01/2001 12:00:00 AM|12/31/9999 12:00:00 AM|CCC|EXAMPLE
;

proc import datafile=tst out=tst replace 
  dbms=dlm 
;
  delimiter='|';
run;

 

Not sure why the LIST statement is no longer treating 'A0'X as a character that forces it to show the hex codes for the line.  Perhaps you have the 'A0'x character after the last variable name?  Microsoft thinks this character means a non-breaking space and sticks into at a lot of unwanted spaces.  Try this code to see the last character on the first line of the file.

data _null_;
   infile '...file...' obs=1 ;
   input;
   char=substr(_infile_,length(_infile_),1);
   put char= $hex2. ;
run;

 

tropicalsurfer
Fluorite | Level 6

It is due to the termstr format of the text file. This macro determined which one the file uses and imports properly for both crlf and lf.

 

%macro initial(file, handle_name, other_filename_options=) ;


/* if there is a carriage return at the end, then return 1 (stored in macro variable SYSRC) */
%sysexec head -n 1 "&file" | awk '/\r$/ { exit(1) }' ;
%if &SYsrc=1 %then %let termstr=crlf ;
%else %let termstr=lf ;
filename &handle_name "&file" termstr=&termstr &other_filename_options ;
options mprint;


%mend ;

%initial(file=data1, handle_name=A);

 

proc import DATAFILE=A DBMS=DLM REPLACE OUT=Imported_Data ;
DELIMITER='|' ; GUESSINGROWS=32767;
run ;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 21 replies
  • 2462 views
  • 0 likes
  • 5 in conversation