Desktop productivity for business analysts and programmers

Proc Import Text file, not labeling last variable name

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Proc Import Text file, not labeling last variable name

[ Edited ]

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;


Accepted Solutions
Solution
‎04-19-2016 07:59 PM
Occasional Contributor
Posts: 8

Re: Proc Import Text file, not labeling last variable name

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


All Replies
Respected Advisor
Posts: 3,775

Re: Proc Import Text file, not labeling last variable name

Show the data.

 

Show the SAS generated datastep.

Grand Advisor
Posts: 17,308

Re: Proc Import Text file, not labeling last variable name

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.

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

[ Edited ]

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.

Grand Advisor
Posts: 17,308

Re: Proc Import Text file, not labeling last variable name

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

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

[ Edited ]

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

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

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

Grand Advisor
Posts: 17,308

Re: Proc Import Text file, not labeling last variable name

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

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

Sorry, new at this Smiley Happy

 

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;

Grand Advisor
Posts: 17,308

Re: Proc Import Text file, not labeling last variable name

Change MISSOVER to TRUNCOVER and try that.

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

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;

Grand Advisor
Posts: 17,308

Re: Proc Import Text file, not labeling last variable name

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

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

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

Grand Advisor
Posts: 17,308

Re: Proc Import Text file, not labeling last variable name

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

 

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?

Occasional Contributor
Posts: 19

Re: Proc Import Text file, not labeling last variable name

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 647 views
  • 0 likes
  • 5 in conversation