Counting ....Do Loops

[sniggih]

I have claims that have are multiple lines (or records) to each claim_number. I need to add a count based on the billed amount. The highest billed per line on each claim will get a 1. Then the next highest 2 etc. Then with each new claim_number the count will need to be reset to 1. I am not sure what to do...since I rarely use SAS. Any assistance would be greatly appreciated. Thank you.

 

[jj2339]

This is an addendum to what was written above. The above was written by my boss. We're a bit perplexed on this issue. Here is the code I have tried to come up with, which is not working.

data util.throut;
set util.throut;
do record = 1 to 11037;
a=1;
counter=a;
x=claim;
do until (x^=claim);
sequence=counter;
a=counter;
counter=a+1;
end;
end;

I am pulling claims from a table called 'throut', and am populating a field called sequence. What I want to do is assign each line of the claim a number (the table is already sequenced in the order I want to go, and there are a total of 11,037 records)

For instance, claim A has 3 lines, so I need to assign line 1 a sequence value of 1, line 2 a sequence value of 2, etc. Then when claim B comes up on record 4, it would start over again at 1, and start the count all over again.

When I run the code above, it loops endlessly, so I assume it's getting stuck in the first loop and never exiting when the claim # changes. Or else it isn't changing records. Anyway, any help from anyone would be very much appreciated.

 

[teralearner]

data claim;
input claim $ amount;
datalines;
1 25.45
1 34.67
2 76.88
2 89.00
2 100.01
;
proc sort data = claim;
by claim descending amount;
run;

data claim;
set claim;
by claim;
if first.claim then count = 1;
else count+1;
run;

 

[teralearner]

If your data is already sorted the way you wanted, you do not need to sort it again.

data claim;
set claim;
by claim; *This BY statement allows you to use first.variable;
if first.claim then count = 1; *Set count to 1 everytime claim number changes;
else count+1; *Increase count by 1 if claim number stays the same;
run;

 

[jj2339]

Thanks for those tips, but they didn't seem to work. The first one never completed, and the second one populated everything with a 1. I did try the below, and this pulled up zero records for some reason, and didn't do anything...I hardcoded the number of records in below, to do the nested 'do' loops.

data util.throut;
set util.throut;
by claim;
do record = 1 to 11037;
counter = 1;
x=claim;
do until (b = "stop"
x2 = claim; /* next claim */
if x ^= x2 then do;
counter=1;
b = "stop";
sequence=counter;
counter=counter+1;

end;
end;
end;
run;


any idea why this wouldn't work?

 

[jj2339]

Sorry, I tried something else, and the first one did work. I'm going to play with it awhile, but thanks for the syntax!

 

[teralearner]

There is no second one!!! They are the same. If your data is not sorted, you need the sort, otherwise you only need the data step.

 

[unifex]

I don't know if you got this sorted yet but the last bit of code certainly won't work unfortunately.

The main problem with it is that SAS will not output any observations until it reaches the end of the step (the run statement) unless you specify an 'output' statement in the code. It also won't read the next input record until it hits the set statement. What your code is doing is looping around and around the first input record and not outputting any of them (although i would have expected 1 record to be output at the very end - unless of course you abort the step).

The datastep sbmitted by Teralearner should work. Make sure that the "Else count+1;" is coded as is, this implies a RETAIN of the value of count from the previous record. If you say "count=count+1" then it will reset count to missing each time it reads a new input record.

Hope this helps!