Help using Base SAS procedures

data import acting strange

Reply
Occasional Contributor
Posts: 9

data import acting strange

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.






Super User
Super User
Posts: 7,413

Re: data import acting strange

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.

Occasional Contributor
Posts: 9

Re: data import acting strange

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.

Super User
Super User
Posts: 6,502

Re: data import acting strange

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.


Super User
Super User
Posts: 7,413

Re: data import acting strange

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.

Ask a Question
Discussion stats
  • 4 replies
  • 303 views
  • 0 likes
  • 3 in conversation