topic Re: PROC IMPORT encounters problem when contains a " in SAS Programming

PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 02:41:20 GMT

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

Re: PROC IMPORT encounters problem when contains a "

Reeza — Tue, 28 Jun 2016 04:31:41 GMT

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

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 04:37:09 GMT

Everything is ok in log.

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

Re: PROC IMPORT encounters problem when contains a "

Ksharp — Tue, 28 Jun 2016 05:03:20 GMT

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

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 05:29:02 GMT

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

Re: PROC IMPORT encounters problem when contains a "

Reeza — Tue, 28 Jun 2016 05:36:08 GMT

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;

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 05:48:18 GMT

Thanks a lot! This does work.

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

Re: PROC IMPORT encounters problem when contains a "

Reeza — Tue, 28 Jun 2016 05:49:53 GMT

Why?

Re: PROC IMPORT encounters problem when contains a "

Ksharp — Tue, 28 Jun 2016 05:50:30 GMT

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;

Re: PROC IMPORT encounters problem when contains a "

Ksharp — Tue, 28 Jun 2016 05:54:54 GMT

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;

Re: PROC IMPORT encounters problem when contains a "

Reeza — Tue, 28 Jun 2016 06:00:28 GMT

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

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 06:32:55 GMT

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.

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 06:39:10 GMT

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).

Re: PROC IMPORT encounters problem when contains a "

Kurt_Bremser — Tue, 28 Jun 2016 06:57:44 GMT

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

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 07:06:45 GMT

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.

Re: PROC IMPORT encounters problem when contains a "

Kurt_Bremser — Tue, 28 Jun 2016 07:19:50 GMT

@caibird wrote:

So I need proc import.

No, you don't.

proc import is a helper for a quick first shot, but not meant to be used in a production environment, as it will mask changes in input data that will bite you in the behind later on. It also has to make guesses, and these may fail (as you just experience).

Computers are dumb, and if you let them make your work for you, you must be prepared to live with the consequences.

For importing data into SAS, rely on clearly defined structures and put those into data steps that do what you want, not what the computer thinks you want.

I repeat: computers are dumb.

That's why we get paid the big bucks to make them do things that make sense 😉

Re: PROC IMPORT encounters problem when contains a "

caibird — Tue, 28 Jun 2016 07:40:02 GMT

I don't need the high road statement at the moment, I need the problem solved. Besides I don't agree with you, see below:

I totally understand program is not as flexible as human brain, that's why I explicitly told it the DLM=','.

I told the PROC IMPORT all the elements needed to separate words, but it still failed, obvious it is human brain problem, either I use it wrong/missing syntax, or the PROC IMPORT has bug.

Re: PROC IMPORT encounters problem when contains a "

Kurt_Bremser — Tue, 28 Jun 2016 09:08:55 GMT

I'm with you that proc import behaves wrongly because the delimiter=','; statement should make it recognize that there are more columns than only one present. Raise an issue with SAS TS about that.

The fact that this bug is already present in my rather old 9.2 shows that it was obviously not caught by anybody for a long time, which illustrates our well-meant advice to NOT rely on proc import for heavy-duty work. Because none of the heavy SAS users use it, or they would have stumbled across this.

In the meantime, follow the advice given and write a working data step for cases where you find those quotes. data step logic is a part of SAS from the very beginning, you won't find many pieces of software that have been so rigorously tested by endusers and therefore are literally bug-free.

Re: PROC IMPORT encounters problem when contains a "

Reeza — Tue, 28 Jun 2016 09:11:59 GMT

As is, I don't think what you want is possible with proc import, since the DSD option appears by default. This would be correct in 99% of cases. You can contact tech support to verify this.

There are probably ways to automate the generation of the data step code to deal with varying number of variables depending on your rules. But since you need a proc import that won't meet your requirement.

Good Luck.

Re: PROC IMPORT encounters problem when contains a "

Reeza — Tue, 28 Jun 2016 09:21:32 GMT

I respectfully disagree that proc import has a bug.

It's a guessing proc and typically the DSD option would be correct.

It would be nice to have the option to suppress that however.