common variables and obs in as library

BrahmanandaRao — Sun, 14 Feb 2021 09:59:36 GMT

I know to compare datasets with proc compare

How to find common variables and observations in a library any other method

Re: common variables and obs in as library

ballardw — Sun, 14 Feb 2021 10:40:43 GMT

SAS maintains metadata about current assigned libraries in a number of views.

SASHELP.VTABLE has this information about all the data sets:

   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   dbms_memtype char(32) label='DBMS Member Type',
   memlabel char(256) label='Data Set Label',
   typemem char(8) label='Data Set Type',
   crdate num format=DATETIME informat=DATETIME label='Date Created',
   modate num format=DATETIME informat=DATETIME label='Date Modified',
   nobs num label='Number of Physical Observations',
   obslen num label='Observation Length',
   nvar num label='Number of Variables',
   protect char(3) label='Type of Password Protection',
   compress char(8) label='Compression Routine',
   encrypt char(8) label='Encryption',
   npage num label='Number of Pages',
   filesize num label='Size of File',
   pcompress num label='Percent Compression',
   reuse char(3) label='Reuse Space',
   bufsize num label='Bufsize',
   delobs num label='Number of Deleted Observations',
   nlobs num label='Number of Logical Observations',
   maxvar num label='Longest variable name',
   maxlabel num label='Longest label',
   maxgen num label='Maximum number of generations',
   gen num label='Generation number',
   attr char(3) label='Data Set Attributes',
   indxtype char(9) label='Type of Indexes',
   datarep char(32) label='Data Representation',
   sortname char(8) label='Name of Collating Sequence',
   sorttype char(4) label='Sorting Type',
   sortchar char(8) label='Charset Sorted By',
   reqvector char(24) format=$HEX48 informat=$HEX48 label='Requirements Vector',
   datarepname char(170) label='Data Representation Name',
   encoding char(256) label='Data Encoding',
   audit char(3) label='Audit Trail Active?',
   audit_before char(3) label='Audit Before Image?',
   audit_admin char(3) label='Audit Admin Image?',
   audit_error char(3) label='Audit Error Image?',
   audit_data char(3) label='Audit Data Image?',
   num_character num label='Number of Character Variables',
   num_numeric num label='Number of Numeric Variables',
   diagnostic char(256) label='Diagnostic Message from File Open Attempt'

SASHELP.VCOLUMN has

   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   name char(32) label='Column Name',
   type char(4) label='Column Type',
   length num label='Column Length',
   npos num label='Column Position',
   varnum num label='Column Number in Table',
   label char(256) label='Column Label',
   format char(49) label='Column Format',
   informat char(49) label='Column Informat',
   idxusage char(9) label='Column Index Type',
   sortedby num label='Order in Key Sequence',
   xtype char(12) label='Extended Type',
   notnull char(3) label='Not NULL?',
   precision num label='Precision',
   scale num label='Scale',
   transcode char(3) label='Transcoded?',
   diagnostic char(256) label='Diagnostic Message from File Open Attempt'

If you want to use Proc SQL you can also reference these as Dictionary.tables and Dictionary.columns.

You may find this a helpful starting point if the question is "common variables".

proc tabulate data=sashelp.vcolumn;
   where libname='WORK' and memtype='DATA';
   class memname name type;
   table name*type,
         memname
        /misstext=' '
   ;
run;

Replace WORK with the library name you are interested in. Since the libname is stored in upper case you want to use upper case here. This will create a report with the rows the name of variables with the type, character or numeric nested and columns the names of data sets in that library as columns. There will be a 1 indicating the intersection (a count of how many times that variable appears in that data set ).

topic Re: common variables and obs in as library in SAS Programming

common variables and obs in as library

Re: common variables and obs in as library