turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Incrementing data

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 10:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 10:18 AM

Format your code so it's readable.

You probably have multiple account numbers for a specific date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 10:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 10:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 10:51 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 10:59 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 11:24 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 11:27 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 11:44 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 11:50 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 12:30 PM

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 --<-----

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-13-2014 02:18 PM

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.