Date:         Sat, 3 Feb 1996 17:47:13 EDT
Reply-To:     whitloi1@WESTATPO.WESTAT.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Ian Whitlock <whitloi1@WESTATPO.WESTAT.COM>
Subject:      Re: Variables in Multiple Datasets

    Subject: Re: Variables in Multiple Datasets
    Summary: SQL and the SAS system's DICTIONARY files are handy.
    Respondent: Ian Whitlock <whitloi1@westat.com>
 
    Both Joel Achtenberg <joel@WUBIOS.WUSTL.EDU> under the subject above
    and Christopher Zogby <chris@PHOR.COM> under the subject "Macro to list
    records" quote Prasad Ravi
 
    >  How can one go about checking if a particular variable exist in each
    >  dataset of the study or not?, secondly how can I compare variables
    >  across all datasets?.
 
    (I haven't received the original question yet.)  Joel suggests that he
    has relevant code and Christopher presents a macro FINDDATA that runs
    over two pages.  I see the question as inherently simple when one has
    the right tools.  In this case it is to use a little macro with SQL and
    the DICTIONARY.COLUMNS (although the use of the output from CONTENTS is
    also possible).
 
    The macro below, FINDVARS, takes a small list of variables (up to 200
    bytes) and a libref.  It then lists the variables, which data sets in
    the library have the variables, and how many data sets in the library
    have the variables.
 
          %macro findvars ( lib = , vlist = ) ;
          options nolabel mprint ;
 
          %let vlist = %upcase ( &vlist ) ;
 
          data __vars ( keep = name ) ;
             length name $ 8 ;
             do i = 1 to 200 until ( scan ( "&vlist" , i ) = ' ' ) ;
                name = scan ( "&vlist" , i ) ;
                if name ^= ' ' then output __vars ;
             end ;
          run ;
 
          proc sql ;
             select  f.name , v.memname ,
                     count ( distinct v.memname ) as found
                from __vars as f left join
                     ( select memname , name
                         from dictionary.columns
                         where  libname = upcase("&lib") and
                         memtype = 'DATA' and
                         " &vlist " contains ' '||trim(name)||' '
                     ) as v
                on  f.name = v.name
                group by f.name
                order by f.name , v.memname
             ;
          quit ;
          options label ;
          %mend  findvars ;
 
    The DATA step creates WORK.__VARS holding the names given in the
    parameter VLIST.  When run fresh under SAS 6.11 under windows the test
    code
 
          data w ; retain x y za 2 ; run ;
          %findvars ( lib = work , vlist = a x y z name )
 
    produced the report
 
          NAME      MEMNAME      FOUND
          ----------------------------
          A                          0
          NAME      __VARS           1
          X         W                1
          Y         W                1
          Z                          0
 
    The same result should be produced under any operating system that
    recognizes the DICTIONARY files.  If one wants more characteristics of
    the variables, then simply add them to the SELECT statement.  If one
    wants the physical path for the libref that can be found in another
    DICTIONARY file (see Technical Report P-222).
 
    If one is interested in more than one library, then either drop the
    LIBNAME restriction or modify it in a manner similar to that used for
    VLIST.  To remove the 200 byte restriction on VLIST, I would use macro
    code to generate the DATA step creating __VARS and make VLIST hold the
    name of a macro variable with the variable list instead of making it be
    the list.
 
    Ian Whitlock <whitloi1@westat.com>