DATA Step, Macro, Functions and more

Data Step with Do Loop is exiting after processing first record

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Data Step with Do Loop is exiting after processing first record

I am working with a data set that has a total amount that I want to compare to subtotal amounts that are stored horizontally across columns in 20 variables called fund_net_prem01 through fund_net_prem20.

I want to add up the subtotals until they match the total, and then capture the number of subtotals it took to do that. The problem I'm solving is that there is bad data in those subtotals... so if the total is $20,000 and the first subtotal is $20,000, but there's also a 2nd and 3rd subtotal for $5,000 each I want to know that there's only one subtotal and record that and then ignore the other ones.

In order to do this I'm putting the subtotals into an array, doing a cumulative sum in a do loop, and then outputting the record once the cumulative sum equals the total, and using the counter variable in the do loop to tell me how many subtotals (and then exit the do loop and go to the next record). See code below:

     array fnp {20} fund_net_prem_01-fund_net_prem_20;

     do i=1 to 20;

           pnffundamtsum+fnp;

           fundcount=i;

           if pnffundamtsum = gross_amt then do;

                output;

                leave;

           end;

     end;

This is working quite nicely until the going to the next record part. I'm only able to parse the first record and then the entire data step stops. Any ideas why?

I'd never used the 'leave' statement before to exit the do loop, but it has nothing to do with that. The data step was only processing one record before I added it.


Accepted Solutions
Solution
‎02-05-2014 10:26 AM
Respected Advisor
Posts: 3,777

Re: Data Step with Do Loop is exiting after processing first record

Does it really stop or just not output after the first record?  Show the NOTE:s

Maybe you need to initialize PNFFUNDAMTSUM back to missing at each new iteration of the data step loop.  Seeing the rest of the code might help too.

View solution in original post


All Replies
Solution
‎02-05-2014 10:26 AM
Respected Advisor
Posts: 3,777

Re: Data Step with Do Loop is exiting after processing first record

Does it really stop or just not output after the first record?  Show the NOTE:s

Maybe you need to initialize PNFFUNDAMTSUM back to missing at each new iteration of the data step loop.  Seeing the rest of the code might help too.

Contributor
Posts: 69

Re: Data Step with Do Loop is exiting after processing first record

I believe it stops. There are few notes- only the usual number of obs read, outputted, compression, and processing time.

There's not much other code.

However... you were right that the pnffundamtsum variable needed to be re-initialized! Thank you. It was retaining the value and causing the 2nd record to start at a number not equal to zero.

Trusted Advisor
Posts: 1,630

Re: Data Step with Do Loop is exiting after processing first record

Without knowing your data, I would guess the problem is:

if pnffundamtsum = gross_amt then do;

When pnffundamtsm equals gross_amt, the output statement is executed. If on a given record, pnffundamtsum never equals gross_amt, then no record is output.

If this guess is right, the data step do loop is indeed executing for every observation, but only outputting for a small number of observations. It is not exiting after the first observation.

Contributor
Posts: 69

Re: Data Step with Do Loop is exiting after processing first record

You were correct- it was processing for every observation. The compare was not working because I didn't re-initialize.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 264 views
  • 3 likes
  • 3 in conversation