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.
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.