BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cau83
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

4 REPLIES 4
data_null__
Jade | Level 19

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.

cau83
Pyrite | Level 9

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.

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
cau83
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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