topic Re: Variables Check in SAS Programming

Variables Check

SASPhile — Thu, 07 Jun 2018 15:58:06 GMT

Dataset:BU
Business_Unit       Dataset       Variables
Credit               Dsn1          Var1
Credit              Dsn1          Var2
Credit              Dsn1          Var3
Credit              Dsn2          Var1
Credit              Dsn2          Var3

Dataset:Lookup

Business_Unit       Variables
Credit              Var1
Credit              Var2
Credit              Var3

Dataset:Qc1

Business_Unit       Dataset         Missing_Variable
Credit               Dsn2             Var2

Dataset lookup has list of business units and expected variables. So business unit Credit must have three variables Var1 Var2 and Var3

Dataset BU, the dataset Dsn2 has one variable missing Var2 for the business unit Credit. I would like to report to QC1 dataset as above

Re: Variables Check

gamotte — Thu, 07 Jun 2018 16:37:10 GMT

Hello,

Something like this ?

Data BU;
    infile datalines dlm=',';
    input Business_Unit $ Dataset $ Variables $;
    datalines;
Credit,Dsn1,Var1
Credit,Dsn1,Var2
Credit,Dsn1,Var3
Credit,Dsn2,Var1
Credit,Dsn2,Var3    
;
run;

Data Lookup;
    infile datalines dlm=',';
    input Business_Unit $ Variables $;
    datalines;
Credit,Var1
Credit,Var2
Credit,Var3
;
run;

proc sql;
    CREATE TABLE Qc1 AS
    SELECT a.Business_Unit, a.Dataset, b.Variables
    FROM BU a
    LEFT JOIN Lookup b
    ON b.Business_Unit=a.Business_Unit

    EXCEPT 

    SELECT * FROM BU;
quit;

Re: Variables Check

ballardw — Thu, 07 Jun 2018 17:14:10 GMT

You can find the names and properties of variables in Dictionary.columns if using Proc Sql (probably best for this exercise) or sashelp.vcolumn.

For instance the following code gets the variables from SASHELP.CLASS.

proc sql;
   select * 
   from dictionary.columns
   where  libname='SASHELP' and memname='CLASS'
   ;
quit;

Caveats: the libname and memname (dataset name) are stored in uppercase but the name (variable name) may be mixed case so you need to address that. If you have as many libraries and data sets as I do you might want to create a subset dictionary.columns to a single library if you are going to use it repeatedly.

And here is brief example of comparing things:

data work.class;
   set sashelp.class;
   drop age;
run;

proc sql;
   select * from (select upcase(name) as name from dictionary.columns
      where  libname='SASHELP' and memname='CLASS')
   except
   select * from (select upcase(name) as name from dictionary.columns
      where  libname='WORK' and memname='CLASS')
   ;
quit;

Your data set would likely take the place of the bit above where I extract names from dictionary.columns

Since you have a data set name then you would also need to match on the name instead of the explicit memname I used. You would need to provide mapping from "business_unit" to terms of SAS libraries or data sets.