Help using Base SAS procedures

trouble importing raw text file (3 columns only)

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

trouble importing raw text file (3 columns only)

I've never been too good at importing raw text files Smiley Sad 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;

Attachment

Accepted Solutions
Solution
‎01-30-2012 06:21 PM
Trusted Advisor
Posts: 1,301

trouble importing raw text file (3 columns only)

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;

View solution in original post


All Replies
Contributor
Posts: 42

trouble importing raw text file (3 columns only)

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

Solution
‎01-30-2012 06:21 PM
Trusted Advisor
Posts: 1,301

trouble importing raw text file (3 columns only)

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;

Contributor
Posts: 42

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

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?

Trusted Advisor
Posts: 1,301

trouble importing raw text file (3 columns only)

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.

Super User
Super User
Posts: 7,050

trouble importing raw text file (3 columns only)

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;    

Contributor
Posts: 42

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

Thanks Tom, that worked too!

Respected Advisor
Posts: 3,799

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 188 views
  • 3 likes
  • 4 in conversation