PROC IMPORT encounters problem when contains a "

Reply
Occasional Contributor
Posts: 17

PROC IMPORT encounters problem when contains a "

Guys,

What's wrong with my code? Thanks.

 

Raw(txt):
ABC DEF="XXXX,0.000,15.2,19

 

Code:
libname save 'xxxx;
 filename txt 'xxxx\test.txt';

 proc import out=save.test  datafile=txt dbms=dlm replace;
          delimiter=',';
          guessingrows=250;
          datarow=1;
          getnames=no;
 run;

 

Result:
Var1
 ABC DEF="XXXX

 

Expect result:
Var1                    Var2     Var3  Var4
 ABC DEF="XXXX   0.000   15.2   19

Super User
Posts: 19,822

Re: PROC IMPORT encounters problem when contains a "

What does the log say? Have you tried increasing GuessingRows?

Occasional Contributor
Posts: 17

Re: PROC IMPORT encounters problem when contains a "

Everything is ok in log.

Obvious it is not guessing row problem, my .txt contains only 1 row.

Super User
Posts: 10,041

Re: PROC IMPORT encounters problem when contains a "

Copy the code generated by PROC IMPORT from LOG and Paste it in Program Editor and try REMOVE DSD Option .

Occasional Contributor
Posts: 17

Re: PROC IMPORT encounters problem when contains a "

Here is the log, I tried to remove DSD, problem still there.

 

In my main post, I already provide the raw data and code, could anyone help copy/paste/run it to help solve the problem, rather than just saying? Thanks a lot!

 

 

***********************************************************************/
18       data SAVE.TEST    ;
19       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
20       infile TXT delimiter = ',' MISSOVER DSD lrecl=32767 ;
21          informat VAR1 $27. ;
22          format VAR1 $27. ;
23       input
24                   VAR1 $
25       ;
26       if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
27       run;

NOTE: The infile TXT is:
      Filename=xxxx\test.txt,
      RECFM=V,LRECL=32767,File Size (bytes)=27,
      Last Modified=28Jun2016:10:02:27,
      Create Time=28Jun2016:09:53:48

NOTE: 1 record was read from the infile TXT.
      The minimum record length was 27.
      The maximum record length was 27.
NOTE: The data set SAVE.TEST has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


1 rows created in SAVE.TEST from TXT.

 

NOTE: SAVE.TEST data set was successfully created.
NOTE: The data set SAVE.TEST has 1 observations and 1 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.17 seconds
      cpu time            0.14 seconds

Super User
Posts: 19,822

Re: PROC IMPORT encounters problem when contains a "

FYI It does help if you post your data in the same format that you have it. 

 

Data want;
informat var1 $20.; Infile txt dlm=',' Truncover ; Input var1 $ var2 var3 var4; Run;
Occasional Contributor
Posts: 17

Re: PROC IMPORT encounters problem when contains a "

Thanks a lot! This does work.

 

But for some reason, I need to use PROC IMPORT...

Super User
Posts: 19,822

Re: PROC IMPORT encounters problem when contains a "

Why? 

Occasional Contributor
Posts: 17

Re: PROC IMPORT encounters problem when contains a "

Well, the reason I tend to use PROC IMPORT is the raw data row length separated by "," is variable.

 

Besides, I am kind of wondering why PROC IMPORT not work, after all, I specify DLM=',' already, why SAS still need to treat the " special? Isn't it a bug if no solution?

 

I tried guessing one row, but not work.

 

 

Super User
Posts: 10,041

Re: PROC IMPORT encounters problem when contains a "

I just notice that the code genereated by PROC IMPORT is not right. It miss other two variables.

 

 

filename TXT '/folders/myfolders/test03.txt';
data TEST ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile TXT delimiter = ',' MISSOVER lrecl=32767 ;
informat VAR1 $27. ;
format VAR1 $27. ;
input
VAR1 $
VAR2 $
VAR3 $
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

Super User
Posts: 19,822

Re: PROC IMPORT encounters problem when contains a "

Try setting guessing row to 1 would be my only guess. 

Super User
Posts: 10,041

Re: PROC IMPORT encounters problem when contains a "

You didn't remove DSD.

 

18       data SAVE.TEST    ;
19       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
20       infile TXT delimiter = ',' MISSOVER DSD  /* <-- remove it */  lrecl=32767 ;
21          informat VAR1 $27. ;
22          format VAR1 $27. ;
23       input
24                   VAR1 $
25       ;
26       if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
27       run;

Occasional Contributor
Posts: 17

Re: PROC IMPORT encounters problem when contains a "

I just submitted the log from PROC IMPORT to you guys to help check what maybe the cause.

 

While I ran the code, I removed DSD, not work.

 

The way I most need is someone provide a PROC IMPORT code works for a txt content

ABC DEF="XXXX,0.000,15.2,19

 

Simply point out what's the problem in my code also great.

 

Guessing, saying, not much help.  (I appreciate the kindness).

Super User
Posts: 7,827

Re: PROC IMPORT encounters problem when contains a "

Why bother with proc import at all?

It needs to make a guess, and I guess that is simply confused by the double quote.

data test;
infile '$HOME/test.txt' dlm=',';
length var1 $20;
input var1 $ var2 var3 var4;
run;

works perfectly well.

proc print shows this:

Obs        var1         var2    var3    var4

 1     ABC DEF="XXXX      0     15.2     19 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: PROC IMPORT encounters problem when contains a "

Posted in reply to KurtBremser

My raw data column(variable) are not a const number, the example is var1-var4, other data could be var1-var2, var1-var100.

So I need proc import.

Ask a Question
Discussion stats
  • 22 replies
  • 540 views
  • 5 likes
  • 4 in conversation