Incrementing data

Reply
Occasional Contributor
Posts: 8

Incrementing data

Hi all, I need help on this. I ran the following code proc sort data=reslts.sample_2007; by acct_no cur_information_date  ; run; data sample_2007; set reslts.sample_2007 (obs=max); by acct_no ; retain cal_new_days_del; if first.acct_no then do; cal_new_days_del = new_days_del; end; else do; cal_new_days_del = cal_new_days_del + 1; end; date_delq_started = cur_information_date - cal_new_days_del; run; It increment the number  but something strange is happening like 95 is repeated. I have tried googling it but no solution. Do you perhaps seem these before? Any solution. 0000003018285621          86 0000003018285621          87 0000003018285621          88 0000003018285621          89 0000003018285621          89 0000003018285621          90 0000003018285621          91 0000003018285621          92 0000003018285621          93 0000003018285621          94 0000003018285621          95 0000003018285621          95 0000003018285621          96 0000003018285621          97

Super User
Posts: 19,770

Re: Incrementing data

Format your code so it's readable.

You probably have multiple account numbers for a specific date.

Occasional Contributor
Posts: 8

Re: Incrementing data

Hi all, I need help on this. I ran the following code

proc sort data=reslts.sample_2007;

by acct_no cur_information_date  ;

run;

data sample_2007;

set reslts.sample_2007 (obs=max);

by acct_no ;

retain cal_new_days_del;

if first.acct_no then do;

cal_new_days_del = new_days_del;

end;

else do; cal_new_days_del = cal_new_days_del + 1;

end;

date_delq_started = cur_information_date - cal_new_days_del;

run;

It increment the number  but something strange is happening like 95 is repeated. I have tried googling it but no solution. Do you perhaps seem these before? Any solution.

0000003018285621          86

0000003018285621          87

0000003018285621          88

0000003018285621          89

0000003018285621          89

0000003018285621          90

0000003018285621          91

0000003018285621          92

0000003018285621          93

0000003018285621          94

0000003018285621          95

0000003018285621          95

0000003018285621          96

0000003018285621          97

Occasional Contributor
Posts: 8

Re: Incrementing data

even when i exclude the cur_information_date in the sort. i still get the same duplicate

Super User
Posts: 19,770

Re: Incrementing data

I can't see anything wrong with your code.

But I also can't see your data.

I'm assuming that acct_no is a 16 character variable and not numeric?

Can you also post the log?

Super User
Super User
Posts: 7,942

Re: Incrementing data

There must be something else happening before this code, or you data contains other information as I have run what you have posted and see no problem:

data have;
  attrib acct_no format=$200.;
  infile datalines;
  input acct_no $;
datalines;
0000003018285621 
0000003018285621 
0000003018285621  
0000003018285621
0000003018285621  
0000003018285621
0000003018285621  
0000003018285621  
0000003018285621  
0000003018285621    
0000003018285621   
0000003018285621  
0000003018285621    
0000003018285621   
;
run;

data sample_2007;
  set have;
  by acct_no;
  retain cal_new_days_del;
  if first.acct_no then do;
    cal_new_days_del = 1;
  end;
  else do;
    cal_new_days_del=cal_new_days_del+1;
  end;
run;

Regular Contributor
Posts: 217

Re: Incrementing data

Timmy,

It appears to me that '89' and '95' are in your data twice, for the field "new_days_del" for the account number in your example.  New days updates your retained value and your retained value is used to populate the output fields containing the 89 and 95.


Super User
Posts: 19,770

Re: Incrementing data

That only gets set the first time it sees the account number, otherwise is incremented by 1.

Regular Contributor
Posts: 217

Re: Incrementing data

Reeza,

You stated earlier that his code is good and RW9 found nothing wrong with the account number.  If the problem is with the data, which I suspect it is, then first.** code is the issue.  What happens when there are two rows with the same account number and the same dates but different number values.  What if the row with 94 is sorting after the row with 95 because of the date?  row1 would have 95 and the next row would have 94.  94 + 1 is 95.  The same with 89 if 88 is sorting after 89. 88 + 1 is 89.   The first row with the same date has first.*** = 1.  The second row with the same date has first.*** = 0.  I'll try to build test data to prove my thought.

Message was edited by: James Willis

Super User
Posts: 19,770

Re: Incrementing data

I can't see how that situation would occur. Can you generate some data to illustrate it?

Since it's 16 digits my guess is that its a numeric variable not character that is not respecting the first. rule because of floating point issues.

But that's a guess, and I hate guessing. So it's up to the OP to clear it up, I guess.

Trusted Advisor
Posts: 3,211

Re: Incrementing data

Please show the type of data  (numeric/character) you are using in your test. Reeza's guessing although she hates it could be correct.
Seen this behavior before that 1 ^= 1 ?  Yes many times and always been the floating point background.  http://support.sas.com/techsup/technote/ts230.pdf

---->-- ja karman --<-----
Regular Contributor
Posts: 217

Re: Incrementing data

Timmy,

Following Reeza's and Jaap's  lead, and assuming that your input acct_no is a numeric value; and assuming that floating point is your issue, then rounding your input_acct_no into a temorary variable and working with the temporary variable, should solve your problem.  I went to Jaap's link and tested the code and indeed numbers with a decimal point  values do not match to a number of 1.  When you round the numbers 1 with a decimal point then 1 does indeed match to 1.


data _null_;
format w x z a b 8.0 y 16.15;
retain y 0;
a=(1.0/3) * 3;
w=0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 ;
x=0;
z=((1/3) * 3);
b=((w/3) * 3);
do i = 1 to 10;
     x+0.1;
     y = y + .1;
     if i= 10 then do;
       put a= b= w= x= y= z=;
     end;
end;

if round(b) = ((1/3) * 3) then put 'b is equal to 1';
else put 'what happened to b?';
if round(b) = z then put 'b is equal to z';
else put 'what happened to bz?';
if z = 1 then put 'z is equal to 1';
else put 'what happened to z?';

run;


107  if w = 1 then put 'w is equal to 1';
108  else put 'what happened to w?';
109  if x = 1 then put 'x is equal to 1';
110  else put 'what happened to x?';
111  if y = 1 then put 'y is equal to 1';
112  else put 'what happened to y?';
113  if z = 1 then put 'z is equal to 1';
114  else put 'what happened to z?';
115  run;

w=1 x=1 y=1  z=1
what happened to w?
what happened to x?
what happened to y?
z is equal to 1

622  if round(b) = ((1/3) * 3) then put 'b is equal to 1';
623  else put 'what happened to b?';
624  if round(b) = z then put 'b is equal to z';
625  else put 'what happened to bz?';
626  if z = 1 then put 'z is equal to 1';
627  else put 'what happened to z?';
628  if a = 1 then put 'a is equal to 1';
629  else put 'what happened to a?';
630  run;

a=1 b=1 w=1 x=1 y=1.00000000000000 z=1
b is equal to 1
b is equal to z
z is equal to 1
a is equal to 1
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST"
      format.

Ask a Question
Discussion stats
  • 11 replies
  • 295 views
  • 0 likes
  • 5 in conversation