topic Re: Proc Import Text file, not labeling last variable name in SAS Enterprise Guide

Proc Import Text file, not labeling last variable name

tropical_surfer — Mon, 28 Mar 2016 18:57:44 GMT

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;

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

data_null__ — Mon, 28 Mar 2016 20:33:46 GMT

Show the data.

Show the SAS generated datastep.

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

Reeza — Mon, 28 Mar 2016 20:37:02 GMT

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.

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

tropical_surfer — Wed, 30 Mar 2016 21:20:56 GMT

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.

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

Reeza — Wed, 30 Mar 2016 21:30:16 GMT

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

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

tropical_surfer — Wed, 30 Mar 2016 21:46:00 GMT

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

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

tropical_surfer — Wed, 30 Mar 2016 21:44:35 GMT

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

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

Reeza — Wed, 30 Mar 2016 21:48:29 GMT

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

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

tropical_surfer — Wed, 30 Mar 2016 21:53:56 GMT

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;

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

Reeza — Wed, 30 Mar 2016 21:59:29 GMT

Change MISSOVER to TRUNCOVER and try that.

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

tropical_surfer — Wed, 30 Mar 2016 22:02:40 GMT

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;

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

Reeza — Wed, 30 Mar 2016 22:03:22 GMT

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

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

tropical_surfer — Wed, 30 Mar 2016 22:16:01 GMT

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

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

Reeza — Wed, 30 Mar 2016 22:21:14 GMT

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?

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

tropical_surfer — Wed, 30 Mar 2016 22:24:19 GMT

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.

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

Reeza — Wed, 30 Mar 2016 22:27:58 GMT

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?

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

tropical_surfer — Wed, 30 Mar 2016 22:52:58 GMT

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

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

Tom — Wed, 30 Mar 2016 23:41:06 GMT

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 ;

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

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

Reeza — Thu, 31 Mar 2016 00:08:27 GMT

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.

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

tropical_surfer — Thu, 31 Mar 2016 00:20:07 GMT

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.