DATA Step, Macro, Functions and more

Checking datasets in a DB2 database for values; data splitting

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Checking datasets in a DB2 database for values; data splitting

Hey all. Currently working on a (hopefully) brief program. Here's what it needs to do (my code follows):

1. Go to a DB2 database and check the names of all data sets contained therein.

2. In these data sets, check for a common variable (call it var1).

3. With this common variable (var1), check the values.

4. If the value is 1, output to one data set. If 1.2, output to another. If there are values of 1 and 1.2 in a data set, then output to a third data set.

The thing is, there are quite a few data sets. I've got it to pick out the specific library, then pick out the tables with the variable name I'm looking for. However, I can't get it to properly check the value of this variable and then output accordingly. My code is attached. Some code is commented out. Those bits were experimental. Thought I'd leave them in so you could see my thought process.

Thanks guys!


Attachment

Accepted Solutions
Solution
‎05-21-2012 02:52 PM
Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

Jonathon,

I'm assuming you are properly extracting all the relevant data set names into the SAS data set CallAllLibname (if not, we can revisit that piece).  What your program will need to do is actually examine the data within each data set to determine its final destination.  Here's one way to do that.

I'm altering the goal, slightly, to create three macro variables:

&ONE holding a list of all data set names with 1 only

&ONEptTWO holding a list of all data set names with 1.2 only

&ONEandONEptTWO holding a list of all data set names holding both values

proc sql noprint;

   select trim(tbname) into : dataset_list from callalllibname;

   %let nobs = &sqlobs;

quit;

%global ONE ONEptTWO ONEandONEptTWO;

%let ONE=;

%let ONEptTWO=;

%let ONEandONEptTWO=;

%macro test_each;

   %local i next_dataset;

   %do i=1 %to &nobs;

          %let next_dataset = %scan(&dataset_list, &i, %str( ));   /* get name of next data set in the list */

          data _null_;

              do until (one='Y' and onepttwo='Y') or alldone;

                   set &next_dataset (keep=var1) end=alldone;

                   if var1=1 then one='Y';

                   else if var1=1.2 then onepttwo='Y';

             end;

             if one='Y' and onepttwo='Y' then call execute("%let ONEandONEptTWO = &ONEandONEptTWO &next_dataset;");

             else if one='Y' then call execute("%let ONE = &ONE &next_dataset;");

             else if onepttwo='Y' then call execute("%let ONEptTWO = &ONEptTWO &next_dataset;");

          run;

   %end;

%mend;

%test_each

%put _user_;

If this is getting the information you need, but in a somewhat different format, we can adjust it.  But first things first.

The code is untested, so there is some chance that we might need to debug it.

Good luck.

View solution in original post


All Replies
Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

I'd very much appreciate any fresh ideas. I've now been fumbling through this for the last five hours with minimal changes. If any of you folks could provide even a modicum of insight, that'd be great. Just to clarify exactly what I'm trying to do.

1. I have 300+ data sets. All of them have a common variable (var1)

2. This common variable takes on two values: 1 and 1.2.

3. In some data sets, it only has value 1. In others, it only has value 1.2. Still in others, it has value 1 and 1.2.

4. I'd like to find the data sets which have a value of 1 only and output the data set names to another data set. For example, if the data sets PIG, COW, and DOG all have var1 = 1.0, then I want SAS to take the the data set names PIG, COW, and DOG and output them to a fourth data set... call it ONE.

5. If the data sets BEAR, MOM, CAR all have var1 = 1.2, then I want SAS to output the data set names to a separate data set, call it ONEptTWO.

6. Lastly, if the data sets COOK, PAW, TIRE all have var1 = 1.0 or var1 = 1.2 (hundreds of rows in all of these data sets, so some are 1.0 and some are 1.2), then I'd like SAS to ouput the data set names to a separate data set, call it ONEandONEptTWO.

Since there are so many data sets (300+), I don't want to have to insert the data set names separately. This is why I'm attempting to use SQL with a macro.

Thanks,

Jonathon

Solution
‎05-21-2012 02:52 PM
Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

Jonathon,

I'm assuming you are properly extracting all the relevant data set names into the SAS data set CallAllLibname (if not, we can revisit that piece).  What your program will need to do is actually examine the data within each data set to determine its final destination.  Here's one way to do that.

I'm altering the goal, slightly, to create three macro variables:

&ONE holding a list of all data set names with 1 only

&ONEptTWO holding a list of all data set names with 1.2 only

&ONEandONEptTWO holding a list of all data set names holding both values

proc sql noprint;

   select trim(tbname) into : dataset_list from callalllibname;

   %let nobs = &sqlobs;

quit;

%global ONE ONEptTWO ONEandONEptTWO;

%let ONE=;

%let ONEptTWO=;

%let ONEandONEptTWO=;

%macro test_each;

   %local i next_dataset;

   %do i=1 %to &nobs;

          %let next_dataset = %scan(&dataset_list, &i, %str( ));   /* get name of next data set in the list */

          data _null_;

              do until (one='Y' and onepttwo='Y') or alldone;

                   set &next_dataset (keep=var1) end=alldone;

                   if var1=1 then one='Y';

                   else if var1=1.2 then onepttwo='Y';

             end;

             if one='Y' and onepttwo='Y' then call execute("%let ONEandONEptTWO = &ONEandONEptTWO &next_dataset;");

             else if one='Y' then call execute("%let ONE = &ONE &next_dataset;");

             else if onepttwo='Y' then call execute("%let ONEptTWO = &ONEptTWO &next_dataset;");

          run;

   %end;

%mend;

%test_each

%put _user_;

If this is getting the information you need, but in a somewhat different format, we can adjust it.  But first things first.

The code is untested, so there is some chance that we might need to debug it.

Good luck.

Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

Posted in reply to Astounding

Thanks for the input, Astounding! I'll give it a go and get right back to you.

Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

Small changes to make:

the INTO : clause will need "separated by" added, and adding DISTINCT can't hurt:

select distinct(trim(dbname)) into : dataset_list separated by ' ' from CallAllLibname;


Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

Posted in reply to Astounding

Similar error as before. I am getting some slight output now, though. Gives me hope :smileygrin:

Attachment
Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

Posted in reply to Astounding

Alright. A good bit of it seemed to run properly. The log file was a little less clear than I had hoped from the PUT statement, but that could have something to do with what SAS considers a small error near the beginning. I'm attaching the portion of the log which contains this error.

I am able to pick out the data set names correctly. This is verified when I run %PUT &VARLIST. They're all there.

Attachment
Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

You don't show what came before the error.  I suspect there is a missing semicolon, so SAS doesn't see PROC SQL as a separate statement.

Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

Posted in reply to Astounding

Hmmm. Doesn't look like any semi-colons missed on my end. Shouldn't be anything after the &ONE, &ONEptTWO, and &ONEandONEptTWO statements should there?

Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

The descriptions of &ONE, &ONEptTWO, &ONEand&ONEptTWO should not be part of the program.  They're just describing the plan.  The SAS code begins with the %GLOBAL statement, followed by three %LET statements.  Each of those should have its own semicolon.

Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

Posted in reply to Astounding

I think the program is executing properly because I'm now getting the same error message many, many times in one run. It has to do with the library it's attempting to pull the datasets from. For instance, a common issue is that it's stating:

ERROR: File WORK.dataset.DATA does not exist.


ERROR 79-322: Expecting a ;.

ERROR 76-322: Syntax error, statement will be ignored.

This is what happens for each attempt the program makes.

EDIT: Should mention that I'm looking for a specific library name. We'll call it BAGS. So all data sets will be of the form BAGS.dataset

Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

OK, data sets not existing means they are not in the WORK folder.  The SET statement should have a two-part name, like:

set libname.&next_dataset;

Whatever LIBNAME you have used, apply it here.

As far as the syntax error goes, perhaps you are missing a semicolon within the DATA step.  If adding the LIBNAME doesn't fix it all, try posting your code.

Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

Posted in reply to Astounding

Maybe I'm missing the semicolon, but here's my program:

Attachment
Contributor
Posts: 33

Re: Checking datasets in a DB2 database for values; data splitting

On my way home for the day... wife's birthday and all. I appreciate all of your help. SQL and Macros are new for me, so this has been a great learning experience. Will check in tomorrow to see if you've replied.

Super User
Posts: 5,516

Re: Checking datasets in a DB2 database for values; data splitting

Nothing obvious here.  Let's get a few diagnostics.  Move the %put _user_ statement to just before the %mend statement.  Make sure OPTIONS MPRINT; is in place, as well.  That's will probably give enough to work with.

🔒 This topic is solved and locked.

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

Discussion stats
  • 36 replies
  • 698 views
  • 14 likes
  • 3 in conversation