trouble importing raw text file (3 columns only)

[SASguyCO]

I've never been too good at importing raw text files and am having trouble with this simple one.

Only has three columns, however the first one is a date column that is written out in full form ie January 5, 2011, etc...

Need to bring it into SAS EG and have gotten this far below, but it's still not quite right.

Attached is a sample of the raw text file and testfile is the fileref to the actual text file.

Any assistance is greatly appreciated!

data test;

      infile testfile firstobs=2 dsd termstr=crlf;

       *length date $20.;

  input date anydtdte20. cust_acct_nbr visits;

run;

[SASguyCO]

Here is a sample of what's in the text file:

"Date","Account Number (prop6)","Visits"

"January 1, 2011","",31709

"January 1, 2011","10001767",1

"January 1, 2011","10003595",1

"January 1, 2011","1001489",1

"January 1, 2011","10041613",1

"January 13, 2011","52094543",1

"January 13, 2011","52094640",1

"April 10, 2011","",37979

"April 11, 2011","",36743

"April 12, 2011","",36164

"April 13, 2011","",36517

[FriedEgg · Accepted solution]

data foo;

infile cards dsd dlm=',' firstobs=2;

input date :anydtdte21. acct :$ visits;

cards;

"Date","Account Number (prop6)","Visits"

"January 1, 2011","",31709

"January 1, 2011","10001767",1

"January 1, 2011","10003595",1

"January 1, 2011","1001489",1

"January 1, 2011","10041613",1

"January 13, 2011","52094543",1

"January 13, 2011","52094640",1

"April 10, 2011","",37979

"April 11, 2011","",36743

"April 12, 2011","",36164

"April 13, 2011","",36517

;

run;

[SASguyCO]

Thanks FriedEgg!  that's the ticket!

Quick questions though, what is the purpose of the colon before the informat?  You have it again after the acct variable too? 

Last, how come you changed the anydtdte length to be 21?  Was there a reason you chose that length?

[FriedEgg]

INPUT Statement, List

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000144370.htm

:

enables you to specify an informat that the INPUT statement uses to read the variable value. For a character variable, this format modifier reads the value from the next non-blank column until the pointer reaches the next blank column, the defined length of the variable, or the end of the data line, whichever comes first. For a numeric variable, this format modifier reads the value from the next non-blank column until the pointer reaches the next blank column or the end of the data line, whichever comes first.

Tip:	If the length of the variable has not been previously defined, then its value is read and stored with the informat length.
Tip:	The pointer continues to read until the next blank column is reached. However, if the field is longer than the formatted length, then the value is truncated to the length of variable.
See:	Modified List Input
Featured in:	Reading Unaligned Data with Informats and Reading Delimited Data with Modified List Input

The length of the anydtdte format was choosen for no real reason other than it was as long as or longer than the maximum length of the input data for the column.

[Tom]

Use an INFORMAT. Also make sure to format date variable.

data x ;                                                                                                                               

  informat date anydtdte. ;                                                                                                            

  format date yymmdd10.;                                                                                                               

  infile cards dsd truncover firstobs=2;                                                                                               

  input date acct visits;                                                                                                              

  put (_all_) (=);                                                                                                                     

cards;                                                                                                                                 

"Date","Account Number (prop6)","Visits"                                                                                               

"January 1, 2011","",31709                                                                                                             

"January 1, 2011","10001767",1                                                                                                         

"January 1, 2011","10003595",1                                                                                                         

"January 1, 2011","1001489",1                                                                                                          

"January 1, 2011","10041613",1                                                                                                         

"January 13, 2011","52094543",1                                                                                                        

"January 13, 2011","52094640",1                                                                                                        

"April 10, 2011","",37979                                                                                                              

"April 11, 2011","",36743                                                                                                              

"April 12, 2011","",36164                                                                                                              

"April 13, 2011","",36517                                                                                                              

run;    

[SASguyCO]

Thanks Tom, that worked too!

[data_null__]

You can't use "formatted input" with DSD. DSD  must use "list input".  Use an INFORMAT statement to associate an informat for variables as appropiate.