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

Using SAS EG, whenever i try to import a TXT file , using PROC IMPORT, the label of the last column always comes as VAR , even though i use getnames=yes, still the last column does not have label. Im guessing it has to do with the demiliter but how can I change my code to correct this?

 

Thanks!

 

This is the code I'm using:

 

proc import datafile = "filename.txt"
dbms = DLM out = filename replace;
getnames=yes;
delimiter = "|";
run;

1 ACCEPTED SOLUTION

Accepted Solutions
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 ;

View solution in original post

21 REPLIES 21
data_null__
Jade | Level 19

Show the data.

 

Show the SAS generated datastep.

Reeza
Super User

Your data most likely has an issue - either it's short one variable name, or the name is not SAS compatible, or it's a duplicate name, or some other issue. 

 

Basically, examine your data to find the issue.

tropical_surfer
Fluorite | Level 6

It seems to be an issue with the way the delimiter "l" is used in the text files I have, specifically there is no delimiter placed after the end of the last variable name. The column reads in properly other than the var name. I have over 100 files though so it would be helpful it have it import correctly rather then manually changing it.

Reeza
Super User

Can you post some sample data?

If you're on windows here's a quick way to grab a few lines of your data into a file:

https://gist.github.com/statgeek/de01cb34a61e53f2915d

tropical_surfer
Fluorite | Level 6

Yes, I'll post sample data in a minute. I found a similar post regarding what seems to be a similar issue only they have a csv. Im using windows sas, so I don't know if this is relevant but someone mentioned adding termstr=crlf to resolve the problem. I'm not sure if this can be added to my import code, can it? I wasn't able to get it to work.

 

The post I'm refering to is here if anyone is curious: https://communities.sas.com/t5/SAS-Procedures/Proc-import-issue-in-Unix-SAS/td-p/24485

tropical_surfer
Fluorite | Level 6

Here's a sample of my data, thanks everyone for the responses!

 

format EFF_START_DATE datetime. ;
format EFF_END_DATE datetime. ;
format CODE $3. ;
format VAR4 $32. ;

 

Raw .TXT file looks like this:

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

Reeza
Super User

Show all of your code please - infile statement is important, for example all you may need is TRUNCOVER vs MISSOVER option.

tropical_surfer
Fluorite | Level 6

Sorry, new at this 🙂

 

Here's my log:

 

40 data WORK.SAMPLE ;
41 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
42 infile '/documents/IMPORT_DATA.txt' delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;
43 informat EFF_START_DATE anydtdtm40. ;
44 informat EFF_END_DATE anydtdtm40. ;
45 informat CODE $3. ;
46 informat VAR4 $32. ;

47 format EFF_START_DATE datetime. ;
48 format EFF_END_DATE datetime. ;
49 format CODE $3. ;
50 format VAR4 $32. ;
51 input
52 EFF_START_DATE
53 EFF_END_DATE
54 CODE $
55 VAR4 $
56 ;
57 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
58 run;

Reeza
Super User

Change MISSOVER to TRUNCOVER and try that.

tropical_surfer
Fluorite | Level 6

Okay, how can I specify TRUNCOVER in my code? This is what I'm running to get that log.

 

proc import datafile = "/documents/IMPORT_DATA.txt"
dbms = DLM out = sample replace;
getnames=yes;
delimiter = "|";
run;

Reeza
Super User

Use the code from the log. Strip out the line numbers to use it.

tropical_surfer
Fluorite | Level 6

Okay, just ran it with TRUNCOVER, but had no change in the last variable name.

Reeza
Super User

Sorry, this code explicitly calls it VAR4 - change all references of VAR4 to Description manually 🙂

 

This really is the best way to read a text file. Use proc import to generate a semi-correct data step code. Take the code and customize to data. Proc Import guesses - data step is explicit. When copying from log hold down ALT key to be able to select code without line numbers.

 

 

Is the actual field being read properly?

tropical_surfer
Fluorite | Level 6

I am right now, but I have over 100 files so its a bit tedious to do them all manually as the last variable name changes for each file.

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!

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
  • 2235 views
  • 0 likes
  • 5 in conversation