data import acting strange

BecomingSASsy — Tue, 25 Aug 2015 14:07:49 GMT

Hello,

I recently faced a situation where I was having trouble importing data. Here's the first 5 lines from the data I was trying to import:

CLIENT_TYPE,CHANNEL,ACTION

,INDEX_RETENTION

Bread Only,Multiple Channels,Multiple,0.921248326

Bread Only,Self Only,FP - Non Auto FILL,0.698883642

Bread Only,Self Only,PB - Non Auto FILL,0.798451417

Bread Only,Auto Only,Multiple,0.820087791

Bread Only,Pavillion Only,TEAM - High SPEND APP RD,0.84541142

Bread Only,Pavillion Only,Multiple,0.908121088

Bread Only,Pavillion Only,TXN - RD,0.771980886

Bread Only,Self Only,BOOMERS,0.839943968

Bread Only,Self Only,TEAM,0.974325018

Bread Only,Auto Only,TXN - 2nd Auto,0.914277479

Bread Only,Auto Only,TEAM - 2nd Auto,0.944972329

Bread Only,Pavillion Only,"TXN, TEAM, G2DR - HH < $5k and AGGR > $10k RD",0.841434208

Bread Only,Assisted Only,TEAM - Low SPEND APP OP,1.010529119

Bread Only,Self Only,TXN - Over 80,0.935964874

Bread Only,Auto Only,TEAM - 1st Auto,0.761680536

Bread Only,Assisted Only,DO AGGR <$10K and non AGGR <$5,1.010527535

Bread Only,Self Only,Non-Cdn Resident,0.873219785

Bread Only,Assisted Only,Multiple,0.784828751

Bread Only,Auto Only,TXN - 1st Auto,0.70509378

Bread Only,None,Control Cells,0.962816803

Bread Only,Self Only,UNDEFINED,0.908330854

Bread Only,Pavillion Only,G2DR - Non Auto FILL RD,0.764212679

Bread Only,Assisted Only,TEAM - Invalid Phone OP,1.008314268

Bread Only,Assisted Only,DO TXN Team appts,0.907294913

Bread Only,Self Only,Multiple,0.759387426

Bread Only,Assisted Only,DO TXN Team Invalid phone,1.01299577

Bread Only,Self Only,G2DR - Over 80 - Non Auto FILL,0.917663586

Bread Only,Assisted Only,TXN - Invalid Phone OP,0.73602636

Bread Only,Self Only,G2DR - Invalid Phone,1.010516028

Bread Only,Multiple Channels,TEAM - 1st Auto,4.790011787

Bread Only,Self Only,Negative Markets,0.75906557

Bread Only,Multiple Channels,TEAM - 2nd Auto,1.390066301

Bread Only,Self Only,Age <18 or Null,1.011028555

Bread Only,Multiple Channels,TXN - 1st Auto,1.012999652

Bread Only,Multiple Channels,TEAM,1.40757865

Bread Only,Multiple Channels,TEAM - High SPEND APP RD,0.854881681

Bread Only,Multiple Channels,DO AGGR <$10K and non AGGR <$5,0.884167816

Bread Only,Multiple Channels,TEAM - Low SPEND APP OP,0.775130535

Bread Only,Multiple Channels,TXN - RD,1.013799172

Bread Only,Multiple Channels,FP - Non Auto FILL,0.386243929

Bread Only,Multiple Channels,Non-Cdn Resident,1.013221882

Bread Only,Multiple Channels,TXN - Over 80,0.973770824

Bread Only,Multiple Channels,"TXN, TEAM, G2DR - HH < $5k and AGGR > $10k RD",0.996901905

Bread Only,None,Multiple,2.750464423

Bread Only,Pavillion Only,Control Cells,1.024199777

Bread Only,Multiple Channels,BOOMERS,2.02718358

Bread Only,Multiple Channels,TXN - 2nd Auto,0

Bread Only,Multiple Channels,G2DR - Non Auto FILL RD,1.013500352

Bread Only,Multiple Channels,UNDEFINED,1.013499969

Milk Only,Self Only,BOOMERS,0.875

Milk Only,Self Only,Non-Cdn Resident,0.791705525

Milk Only,Self Only,FP - Non Auto FILL,0.789473684

Milk Only,Self Only,TEAM,0.944444444

Milk Only,Self Only,PB - Non Auto FILL,0.984657878

Milk Only,Pavillion Only,TXN - RD,0.875

Milk Only,Pavillion Only,TEAM - High SPEND APP RD,0.893787891

Milk Only,Pavillion Only,"TXN, TEAM, G2DR - HH > $5k RD",0.864428025

Milk Only,Self Only,TXN - Over 80,0.764130098

Milk Only,Self Only,Multiple,0.789320126

Milk Only,Pavillion Only,Multiple,0.755286629

Milk Only,Multiple Channels,Multiple,0.681037654

Milk Only,Self Only,UNDEFINED,0.832594781

Milk Only,Self Only,FP - Auto FILL,0.778415035

Milk Only,None,Control Cells,1

Milk Only,Pavillion Only,G2DR - Non Auto FILL RD,1

Milk Only,Assisted Only,DO TXN Team appts,0.911545441

Milk Only,Auto Only,TEAM - 2nd Auto,1

Milk Only,Auto Only,TEAM - 1st Auto,1

Milk Only,Self Only,PB - Auto FILL,0.713971154

Milk Only,Self Only,Age <18 or Null,1.02348

Milk Only,Self Only,G2DR - Auto FILL,1

Milk Only,Auto Only,TXN - 2nd Auto,1

Milk Only,Auto Only,TXN - 1st Auto,0.981868811

Milk Only,Auto Only,Multiple,0.69849051

Milk Only,Assisted Only,TXN - Invalid Phone OP,1

Milk Only,Assisted Only,DO TXN Team Invalid phone,0.952782118

Milk Only,Assisted Only,Multiple,0.80611394

Milk Only,Assisted Only,TEAM - Low SPEND APP OP,1

Milk Only,Assisted Only,TEAM - Invalid Phone OP,0.772727273

Milk Only,Self Only,G2DR - Over 80 - Non Auto FILL,0.875

Milk Only,Self Only,G2DR - Over 80 - Auto FILL,1

Milk Only,Assisted Only,DO AGGR <$10K and non AGGR <$5,1

Milk Only,Self Only,G2DR - Invalid Phone,0.784207851

Milk Only,Self Only,Negative Markets,1

Milk Only,Multiple Channels,FP - Non Auto FILL,2.5010654

Milk Only,Pavillion Only,"TXN, TEAM, G2DR - HH < $5k and AGGR > $10k RD",0

Milk Only,Multiple Channels,TEAM - 1st Auto,2.901065673

Milk Only,None,Multiple,0.385315377

Milk and Bread,Multiple Channels,Multiple,0.918454478

Milk and Bread,Self Only,FP - Non Auto FILL,0.813558049

Milk and Bread,Self Only,BOOMERS,0.739966542

Milk and Bread,Pavillion Only,Multiple,0.811043262

Milk and Bread,Self Only,Multiple,0.800664847

Milk and Bread,Self Only,TEAM,0.802871834

Milk and Bread,Self Only,PB - Non Auto FILL,0.792301405

Milk and Bread,Self Only,TXN - Over 80,0.758706038

Milk and Bread,Pavillion Only,TXN - RD,0.820538414

Milk and Bread,Pavillion Only,TEAM - High SPEND APP RD,0.811608305

Milk and Bread,Auto Only,Multiple,0.839435323

Milk and Bread,Self Only,Non-Cdn Resident,0.913270871

Milk and Bread,Self Only,UNDEFINED,0.77133685

Milk and Bread,Pavillion Only,"TXN, TEAM, G2DR - HH > $5k RD",0.75890489

Milk and Bread,Assisted Only,Multiple,0.848558573

Milk and Bread,Auto Only,TEAM - 1st Auto,0.723522595

Milk and Bread,Auto Only,TEAM - 2nd Auto,0.725666853

Milk and Bread,Assisted Only,DO TXN Team appts,0.860307807

Milk and Bread,Auto Only,TXN - 1st Auto,0.761000548

Milk and Bread,None,Control Cells,0.754362998

Milk and Bread,Auto Only,TXN - 2nd Auto,0.776522266

Milk and Bread,Self Only,Negative Markets,0.664905481

Milk and Bread,Self Only,G2DR - Over 80 - Non Auto FILL,0.772105015

Milk and Bread,Pavillion Only,G2DR - Non Auto FILL RD,0.806993654

Milk and Bread,Assisted Only,TEAM - Invalid Phone OP,0.749585368

Milk and Bread,Assisted Only,TXN - Invalid Phone OP,0.817491159

Milk and Bread,Assisted Only,TEAM - Low SPEND APP OP,0.774299094

Milk and Bread,Pavillion Only,"TXN, TEAM, G2DR - HH < $5k and AGGR > $10k RD",0.907792031

Milk and Bread,Assisted Only,DO AGGR <$10K and non AGGR <$5,0.812946341

Milk and Bread,Assisted Only,DO TXN Team Invalid phone,0.853931807

Milk and Bread,Self Only,Age <18 or Null,0.946032093

Milk and Bread,Self Only,G2DR - Invalid Phone,0.845464697

Milk and Bread,Multiple Channels,BOOMERS,1.398148966

Milk and Bread,Multiple Channels,TXN - Over 80,0.86038098

Milk and Bread,Multiple Channels,TXN - RD,0.625

Milk and Bread,Multiple Channels,"TXN, TEAM, G2DR - HH > $5k RD",1.019432159

Milk and Bread,Multiple Channels,TEAM - High SPEND APP RD,1.026055371

Here's the code I was using to import:

data ind;

infile 'C:\Ind.csv' dlm=',' dsd missover firstobs=2;

format CLIENT_TYPE $15. CHANNEL $20. ACTION $80. INDEX_RETENTION COMMA12.10;

input CLIENT_TYPE $ CHANNEL $ ACTION $ INDEX_RETENTION;

run;

For some reason, I was getting a blank line at the top despite the fact that I had included FIRSTOBS=2 in my infile line. The first row in the dataset had missing CLIENT_TYPE, INDEX_RETENTION for CHANNEL and MISSING ACTION and INDEX_RETENTION.

Once I changed the FIRSTOBS=3, everything works fine, but I can't figure out why. If anyone's got a clue or can make any suggestions, would be great.

Re: data import acting strange

RW9 — Tue, 25 Aug 2015 14:26:26 GMT

Its the bit:

CLIENT_TYPE,CHANNEL,ACTION

,INDEX_RETENTION

There appears to be a return after ACTION, which treats them next row as obs=2. Once you move to obs=3 then it just ignores the first two rows. Good idea to fix your datasource so that headings are all on one row with no end of line character.

Re: data import acting strange

BecomingSASsy — Tue, 25 Aug 2015 16:37:31 GMT

Thanks @RW9

I was thinking there's a carriage return there somewhere but couldn't see it. How did you figure it out?

I looked at my data in notepad and it looked fine.

Re: data import acting strange

Tom — Tue, 25 Aug 2015 18:43:07 GMT

It is obvious in the posting on SAS communities.

To see embedded control characters in a file the LIST statement is useful. When there are unprintable characters SAS will print a period and display the HEX codes for the whole line in two lines under the row.

filename code temp;

data _null_;

file code termstr=crlf ;

put 'CLIENT_TYPE,CHANNEL,ACTION' '0D'x ',INDEX_RETENTION' ;

put 'Bread Only,Multiple Channels,Multiple,0.921248326';

put 'Bread Only,Self Only,FP - Non Auto FILL,0.698883642';

run;

data _null_;

infile code termstr=crlf obs=5 ;

input ;

list;

run;

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+

1 CHAR CLIENT_TYPE,CHANNEL,ACTION.,INDEX_RETENTION 43

ZONE 4444455555424444444244544402444455545445444

NUMR 3C95E4F4905C381EE5CC1349FEDC9E458F2545E49FE

2 Bread Only,Multiple Channels,Multiple,0.921248326 49

3 Bread Only,Self Only,FP - Non Auto FILL,0.698883642 51

NOTE: 3 records were read from the infile CODE.

The minimum record length was 43.

The maximum record length was 51.

Re: data import acting strange

RW9 — Wed, 26 Aug 2015 08:38:10 GMT

Yes, I tend to use a free HexEdit program, you can grab a portable (i.e. non-install) one free off the web and they are very useful for looking at the real underlying codes.

http://www.nielshorn.net/prog/old/hexed/

Is the one I use.

topic Re: data import acting strange in SAS Procedures

data import acting strange

Re: data import acting strange

Re: data import acting strange

Re: data import acting strange

Re: data import acting strange