how to read txt file into SAS table why I always have problem

[V168]

I run this code,but didn't give me correct answer

[Tom]

Looks good to me.

You just forgot to attach formats to the date variables.  Add a format statement.

format actdt deactdt yymmdd10.;

You didn't attach a format to the Sales variable either, but that shouldn't matter as much as a human can still see what number it represents.  Printing thousands separators or $ prefix or fixed number of decimal places is just cosmetic.

[V168]

data telecom1;
infile 'D:\my file path' dlm=' ' termstr=crlf truncover;
input 
Acctno $ 1-14 
@15-25 Actdt mmddyy10.
@26-36 Deactdt  mmddyy10.
DeactReason $ 41-52
GoodCredit 53-61
RatePlan 62-64
DealerType $ 65-73
Age 74-79
Province $ 80-82
@83-93 Sales comma10.
;
run;

I run this code and get the result. Every thing fromated still give me wrong results. I don't know why. Have you run the code use the txt file I just attached.Just run the txt file, check the result.

[Tom]

You need to read the date fields from column 15 and column 26.  So you need to use @15 and @26.   But you didn't do that. 

I am not sure if you are trying to tell SAS to move to column -10 and column -9 instead?  If so that cannot work as written since you would need to enclose the subtraction inside of parentheses. But of course even if you fixed the syntax you would still get an error you tried to move to a negative column number.

Fix the cursor movement command so it moves to the proper column before reading the next 10 characters as a date value.

@15 Actdt mmddyy10.
@26 Deactdt  mmddyy10.

Fix the positioning for the SALES variable also.

[Tom]

Perhaps you would find it less confusing to just use formatted mode for every variable?

You probably will still need a cursor movement command as I don't think the MMDDYY informat would support a width of 11.

input 
  Acctno $14. 
  Actdt mmddyy10.
 +1
  Deactdt  mmddyy10.
  DeactReason $13.
  GoodCredit 9.
  RatePlan 3.
  DealerType $9.
  Age 5. 
  Province $3.
  Sales comma10.
;

You could also just read that extra space into a dummy variable that you do not keep in the dataset.

input 
  Acctno $14. 
  Actdt mmddyy10.
  dummy $1.
  Deactdt  mmddyy10.
  DeactReason $13.
  GoodCredit 9.
  RatePlan 3.
  DealerType $9.
  Age 5. 
  Province $3.
  Sales comma10.
;
drop dummy ;

[V168]

Thank you so much for working hard on the code. I am very appreciative of all your effort!

[Ksharp]

@Tom ,
Sure. I just want make my code more cute,streamline and readable.

[V168]

@Ksharp 

Thanks for your beautiful code. do you have the code without a the temporary character variables? I have just tried couple of times and still can't figure it out. 

[Ksharp]

@Tom

already gave you the correct code.

Just replace my INPUT statement with Tom's.

data telecom;
infile 'c:\temp\xaa.txt' dlm=' ' termstr=crlf truncover;
input 
input 
Acctno $ 1-14 
@15 Actdt mmddyy10.
@26 Deactdt  mmddyy10.
DeactReason $ 41-52
GoodCredit 53-61
RatePlan 62-64
DealerType $ 65-73
Age 74-79
Province $ 80-82
@83 Sales comma10.
;
/* DO NOT INCLUDE THESE STATEMENTS
Actdt=input(_Actdt,mmddyy12.);
Deactdt=input(_Deactdt,mmddyy12.);
Sales=input(_Sales,dollar32.);
*/
format Actdt Deactdt mmddyy10. Sales dollar8.2;
run;

[V168]

Thank you so much!

[Tom]

The key thing you have learned here is that you cannot use custom informats when using COLUMN MODE input.  So for those variables you need to switch to either FORMATTED MODE or LIST MODE.  But LIST MODE does not work when the field could be totally blank.  

So use FORMATTED MODE when custom informats are needed. 

Frequently you will also need to use the cursor motion commands, @ or +, to move to the right column before using the formatted input.

[V168]

Yes, this is the point. Thanks!

[V168]

in the above project there is a question:Calculate the tenure in days for each account and give its simple statistics. I have code but have some eror can you tell me how to fix:

data project.tenure;
  set project.telecom;
  
  /* Convert activation date to SAS date value */
  Actdt_SAS = input(Actdt, mmddyy10.);
  
  /* Calculate tenure for active accounts */
  if Deactdt = '          ' then do;
    Tenure_Days = intck('day', Actdt_SAS, today());
  end;
  
  /* Calculate tenure for deactivated accounts */
  else do;
    /* Convert deactivation date to SAS date value */
    Deactdt_SAS = input(Deactdt, mmddyy10.);
    Tenure_Days = intck('day', Actdt_SAS, Deactdt_SAS);
  end;
run;

NOTE: Invalid argument to function INPUT at line 182 column 15.
NOTE: Invalid argument to function INPUT at line 192 column 19.
Acctno=1177113886410 Actdt=09/13/2000 Deactdt=01/08/2001 DeactReason=COMP GoodCredit=0 RatePlan=1
DealerType=C1 Age=45 Province=ON Sales=$63.00 Actdt_SAS=. Deactdt_SAS=. Tenure_Days=. _ERROR_=1
_N_=19
NOTE: Invalid argument to function INPUT at line 182 column 15.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
Acctno=1177128264924 Actdt=12/10/1999 Deactdt=. DeactReason=  GoodCredit=1 RatePlan=1
DealerType=B1 Age=38 Province=ON Sales=$178.00 Actdt_SAS=. Deactdt_SAS=. Tenure_Days=. _ERROR_=1
_N_=20
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      82620 at 186:19   19635 at 193:19
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      102255 at 182:15   19635 at 192:19
NOTE: There were 102255 observations read from the data set PROJECT.TELECOM.
NOTE: The data set PROJECT.TENURE has 102255 observations and 13 variables.
NOTE: DATA statement used (Total process time):
      real time           0.09 seconds
      cpu time            0.09 seconds

[Ksharp]

Better start a new session, since this post is a couple of weeks ago.
Form your log, it is said your DATE variable is invalid(a.k.a is missing value).
Try ?? operator.

Actdt_SAS = input(Actdt, mmddyy10.); ----> Actdt_SAS = input(Actdt,?? mmddyy10.);
Deactdt_SAS = input(Deactdt, mmddyy10.); ----> Deactdt_SAS = input(Deactdt, ?? mmddyy10.);

[V168]

ok.