Checking datasets in a DB2 database for values; data splitting

[jtrousd]

Hey Astounding -- here's the error log I'm getting in full. Could it have something to do with my permissions? I left that out yesterday (foolishly, more than likely) because I assumed the program would output only a permissions error if there were no other problems in the code.

[jtrousd]

Astounding,

I've gone ahead and made this change. It seems to run alright now (still getting some permission errors, but the log looks good):

((ONE='Y' and ONEptTWO='Y') OR ALLDONE);

EDIT: I don't know if you get a bunch of notifications that I'm marking answers as helpful/correct. Sorry about that. I removed the 'correct answer' simply because I want to make sure it outputs properly (program is currently still running)

[jtrousd]

The program runs. However, a small issue has occurred. It's choosing to put every single table into every single category. So a table with var1 solely valued at 1.2 would end up in the 1.0 output.

Is this a result of the change I've made?

[Astounding]

Good catch on the parentheses.  DO UNTIL requires them.  My fault on that one.

I thought I could control which %LET statement would execute using CALL EXECUTE, but that method doesn't seem to be working.  Change the last 3 statements in the DATA step to eliminate CALL EXECUTE.  For example:

if one='Y' and onepttwo='Y' then call symput('ONE', "&ONE &next_dataset");

The double quotes around the second parameter are important.  Similar change would be required for the last two statements.  Let me know if you need help on constructing those.

Looks like we're in the home stretch here!

[jtrousd]

Giving it a shot now. As it's running it'll stop on the larger data sets and I'll see a note saying that a string has become more than 262 characters long and that I may have unbalanced quotations. This program usually takes 6-8 minutes to run, and it just started. I'll let you know in a few minutes what I find. Thanks for all your help!

[jtrousd]

Looks like it worked! Going to do some spot checks now to make sure the data sets are reasonable.

[jtrousd]

Spot check returned a data set which had 1.0 in a 1.2 group. It also had a 2.0 in that group, so do you think that's created a problem? I wasn't anticipating this. I've made the following changes and will run the program again:

(after %GLOBAL statement)

%LET TWO=;

(under DATA _NULL_)

DO UNTIL (ONE='Y' AND ONEptTWO='Y' AND TWO='N' OR ALLDONE);

...

ELSE IF VAR1=2.0 THEN TWO='Y';

[Astounding]

No, the presence of 2.0 shouldn't have any impact.  I was a little careless with my sample CALL SYMPUT, mixing and matching the categories.  Here's a more careful version:

if one='Y' and onepttwo='Y' then call symput('ONEandONEptTWO', "&ONEandONEptTWO &next_dataset");

else if one='Y' then call symput('ONE', "&ONE &next_dataset");

else if onepttwo='Y' then call symput('ONEptTWO', "&ONEptTWO &next_datset");

Hopefully, that should handle it (although you have to consider what, if anything, should happen if 2.0 appears).

[jtrousd]

That's the exact code I have. I caught the tiny typo in your previous post and adjusted it to what you have there. Still mixing. Hmm...

EDIT: Interestingly enough, it's actually picking out the data set correctly for ONEandONEptTWO, but it's still putting it in ONEptTWO as well.

However, ONEptTWO does not have any data sets with solely 1.0 (at least from spot checking).

So ONEptTWO looks like it's combining what should  be there with ONEandONEptTWO.

[Astounding]

Double hmmm...

That would happen if the word ELSE were missing from the middle statement.  This seems like a good time to re-post what the code looks like currently.

[jtrousd]

Here you go. I'm going to restart SAS one more time and give it a shot. Maybe there are some stored values wreaking havoc.

EDIT: Restarting SAS didn't fix it.

[Astounding]

It definitely makes no sense.  How could a data set name get added to two of the lists?

To help with diagnostics, add this code just before the RUN statement of the DATA step:

file print notitles;

put "&next_dataset:  "  ONE= ONEptTWO=;

This will at least give you a mapping of what to expect in the final macro variables.  But it still makes no sense that there is still a problem.

[jtrousd]

Definitely stumping. I'll continue plugging away at it and let you know if I make any breakthroughs. You got me this far, so I'm extremely grateful for that!

[jtrousd]

A thought:

I've added your code. As I'm going through it, it tests to see if 1.2 is in the data set.

If Yes, it checks to see if 1.0 is in the data set.

If Yes, it ends.

I wonder, though, if when it sees that it's got a value of 1.2 it immediately %PUTs it into the 1.2 group, then checks to see if it's got a value of 1.0. If it does, it %PUTs it into the 1.0 and 1.2 group. That might explain why it's doing it for data sets with both. I imagine if I changed the order, it would take all data sets with 1.0 and 1.2 and put them into the 1.0 group.

How do I tell it to only take the last state of var1? More clearly, how do I get it to ignore all previous states expect the most recent? Okay, maybe that's not as clear again. If it gets a yes for 1.2, then a yes for 1.0, how do I tell it to NOT %PUT the data set into ONEptTWO and wait to put it into ONEandONEptTWO?

[Astounding]

OK, got it.

One statement needs to be added to the DATA step just before the RUN statement:  STOP;

The problem occurs when both variables are set to Y, ending the DO loop.  Then one of the CALL SYMPUTs executes, but the DATA step doesn't end.  It continues, re-entering the loop and examining any additional records.  STOP will take care of that.