Checking datasets in a DB2 database for values; data splitting

[jtrousd]

You got it! Fantastic, and a simple fix, too. Thank you very much for your help with this. I feel like I've learned more about how queries work in this one program than I had in my graduate degree. Really living up to your name! :smileygrin:

KSharp, I'm going to run your program as well to check. If I can get it to return the same table names, then we're really in business. Will let you know after I get the code in place and make any minor adjustments to suit my needs.

Thanks again, SAS experts!

[jtrousd]

Hm. Doesn't seem to have resolved the problem.

[Ksharp]

Since I don't have DB2 in my hand. So My libname X is a local directory,you should change it into DB2.

libname x v9 'c:\temp\';

data x.a;
input var1 s;
cards;
1 2
1 23
1 43
1 4
;
run;
data x.b;
input var1 ssds;
cards;
1.2 34
1.2 32
1.2 32
1.2  52
1.2 43
1.2 21
;
run;
data x.c;
input var1 q;
cards;
1 32
1 43
1  4
1.2 4
1 54
1.2 43
;
run;
data x.d;
input var1 u;
cards;
1 32
1  32
1 32
1.2 43
1 54
1.2 43
;
run;



%macro check(tname);
proc sql noprint;
 select count(*)  into : n&tname from x.&tname ;
 select sum(var1=1) into : a&tname from x.&tname ;
 select sum(var1=1.2) into : b&tname from x.&tname ;
quit;
%mend check;
%macro tname(tname);
%if &&a&tname = &&n&tname %then %do; 
                         data temp; length name $ 40; name="&tname";run;
                         proc append base=one data=temp force;run;
                        %end;
 %else %if &&b&tname = &&n&tname %then %do; 
                          data temp; length name $ 40; name="&tname";run;
                          proc append base=onepttwo data=temp force;run;
                         %end;
  %else %do; 
            data temp; length name $ 40; name="&tname";run;
            proc append base=oneandtwo data=temp force;run;
        %end;
%mend tname;
options mprint symbolgen mlogic;
data _null_;
 set sashelp.vmember( where=(libname='X' and memtype='DATA'));
 call execute('%check('||strip(memname)||')');
run;
data _null_;
 set sashelp.vmember( where=(libname='X' and memtype='DATA'));
 call execute('%tname('||strip(memname)||')');
run;

Ksharp

[jtrousd]

Hey Ksharp. Thanks for the additional code. I'm running about 300+ data sets out of a specific library. They're on a DB2 server. Should the libname still be DB2, or the library I'm specifically pulling these tables from?

[Ksharp]

Hi. You don't need to pull these tables out from db2 to SAS. The only statement you need change is libname statement. I recode it to make it better and more succinct . Hope you like it.

libname x db2 dsn=db2 user=xx password=xx schema=K ;

%macro check(tname);
proc sql noprint;
create table temp as
 select count(*) as nobs, sum(var1=1) as one,sum(var1=1.2) as onepttwo,"&tname" as tname
  from x.&tname ;
quit;
proc append base=all data=temp force;run;
%mend check;

options mprint symbolgen mlogic;
data _null_;
 set sashelp.vmember( where=(libname='X' and memtype='DATA'));
 call execute('%check('||strip(memname)||')');
run;

data one onepttwo both;
 set all ;
 if one eq nobs then output one;
  else if onepttwo eq nobs then output onepttwo;
   else output  both ;
run;

Ksharp

[jtrousd]

Hey KSharp,

It looks like it's running reasonably well until the end, where it stays that SASHELP.VMEMBER is empty and WORK.ALL does not exist. Ideas?

[Ksharp]

Did you add Schema into libname statement ?

First of all ,running the following single statement.

libname x db2 dsn=db2 user=xx password=xx schema=K ;

And Check X whether you can see some DB2 tables in X library.

If I guess right, You will see nothing, because you must need to add schema ( it must be uppercase) .

After You ensure you can see these tables, then running the following SAS code .

As I expect ,you will get the result.

Good Luck.

Ksharp