Finding a value across all datasets in a library

[LawrenceHW]

I've been playing around with some code I found in a previous topic (https://communities.sas.com/t5/SAS-Programming/Finding-a-value-in-all-datasets-in-all-variables/m-p/...). I'm looking at using PROC IML to perform data checks across a data library as it does seem to be pretty efficient at doing this. However, I'm getting an error but the code I have seems to be working.

proc iml;
  dsn='work.'+datasets('work');
  do i=1 to nrow(dsn);
    use (dsn[i]);
    read all var _char_ into x[c=vnames];
    flag=(find(x,'DATASETS')^=0);
    do j=1 to nrow(flag);
      loc=loc(flag[j,]);
      if ^isempty(loc) then do;
        temp=vnames[loc];
        vname=vname//temp;
        obs=obs//repeat(j,nrow(temp));
        table=table//repeat(dsn[i],nrow(temp));
      end;
    end;
    close (dsn[i]);
  end;

  create want var {table vname obs};
  append;
  close;
quit;

proc print noobs;run;

I get a dataset out which shows me the datasets, variables and observation numbers for variables that contain "DATASETS". The problem is I get an error in the LOG:

42         proc iml;
NOTE: IML Ready
43           dsn='work.'+datasets('work');
44           do i=1 to nrow(dsn);
45             use (dsn[i]);
46             read all var _char_ into x[c=vnames];
47             flag=(find(x,'DATASETS')^=0);
48             do j=1 to nrow(flag);
49               loc=loc(flag[j,]);
50               if ^isempty(loc) then do;
51                 temp=vnames[loc];
52                 vname=vname//temp;
53                 obs=obs//repeat(j,nrow(temp));
54                 table=table//repeat(dsn[i],nrow(temp));
55               end;
56             end;
57             close (dsn[i]);
58           end;
NOTE: Module ISEMPTY loaded from the storage SASHELP.IMLMLIB.
ERROR: No character variables in the data set.

 statement : READ at line 46 column 5
59         
60           create want var {table vname obs};
61           append;
62           close;
NOTE: Closing WORK.WANT
NOTE: The data set WORK.WANT has 43 observations and 3 variables.
63         quit;
NOTE: Exiting IML.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IML used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds

Anyone got any ideas what is causing this error?

I like the potential for IML to allow me to look across multiple datasets for values as it appears to be a pretty code-efficient method.

Regards,

Lawrence

[ballardw]

The message seems fairly clear, you are looking for a character value in at least one dataset that has only numeric variables.

You might test my theory by creating a library with 3 data sets one of which only has exactly one variable that is numeric and run the code against library. See if you get the same error.

[Rick_SAS]

As you can see from the ERROR, the problem is that you are trying to read the character variables in a data set that has none.

I think the easiest way is to use the SASHELP.VCOLUMN data set to determine which data sets have at least one character variable. Here's one way to get the data set names and variable names that correspond only to character variables:

proc iml;
use sashelp.vcolumn (where=(libname="WORK"));
   names = {memname memtype name type};
   read all var names into DS;
close;

/* exclude any variables that are numeric and any data views */
mattrib DS colname=names;
keepIdx = loc( DS[,'MEMTYPE']='DATA' & DS[,'TYPE']='char' );
charDS = DS[keepIdx, ];
/* now you can loop over the unique values of the MEMNAME column
   and use the variables in the NAME column */
print charDS;

[LawrenceHW]

Hi Rick,

Thanks for that. Looks a much neater way than code I was using. I'll have to test that sometime soon. Not long after posting my original query I had worked that out. The error message is not as informative as a normal SAS one as it doesn't give the information about what dataset it's not reading in. Seems like another set of error messages to interpret

PROC IML does look to have some serious advantages when looking across a whole data library. Not being a statistician I've never really considered IML but the more I read of your blog and these kind of posts, the more interested I get in it especially for data checking.

Thanks again,

Lawrence

[Rick_SAS]

Agreed. Incidentally, this behavior is not unique to PROC IML. You get the same error if you try to use the SET statement in a DATA set to read the character variables when there are no character variables in the data:

data AllNum;
   x=1; y=2; z=3;
run;

data KeepTheChar;
   set AllNum(keep=_CHARACTER_);
   /* ERROR: The variable _CHARACTER_ in the DROP, KEEP, or RENAME
       list has never been referenced.*/
run;

proc iml;
use AllNum;
read all var _CHAR_ into X; /* ERROR: No character variables in the data set. */
close;

Although the error messages are different, the data set name is not part of either one. So if you were using the DATA step inside of a macro loop, you would encounter the same question: Which data set does not have any character variables?  Of course, in IML you can just put

PRINT (dsn[i]);

before you try to read the data, thereby revealing the name of the problem data set.

[LawrenceHW]

Thanks Rick. I've never encountered that error message and am still fumbling round the edges of PROC IML ... if only there was a book which introduced SAS/IML