topic trouble importing raw text file (3 columns only) in SAS Procedures

trouble importing raw text file (3 columns only)

SASguyCO — Mon, 30 Jan 2012 23:05:03 GMT

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;

trouble importing raw text file (3 columns only)

SASguyCO — Mon, 30 Jan 2012 23:09:03 GMT

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

trouble importing raw text file (3 columns only)

FriedEgg — Mon, 30 Jan 2012 23:21:32 GMT

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;

Re: trouble importing raw text file (3 columns only)

data_null__ — Mon, 30 Jan 2012 23:21:46 GMT

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.

trouble importing raw text file (3 columns only)

Tom — Mon, 30 Jan 2012 23:24:43 GMT

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;

Re: trouble importing raw text file (3 columns only)

SASguyCO — Mon, 30 Jan 2012 23:38:27 GMT

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?

trouble importing raw text file (3 columns only)

FriedEgg — Mon, 30 Jan 2012 23:47:48 GMT

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.

Re: trouble importing raw text file (3 columns only)

SASguyCO — Mon, 30 Jan 2012 23:48:17 GMT

Thanks Tom, that worked too!