2 weeks ago
Business_Unit Dataset Variables
Credit Dsn1 Var1
Credit Dsn1 Var2
Credit Dsn1 Var3
Credit Dsn2 Var1
Credit Dsn2 Var3
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
2 weeks ago
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;
2 weeks ago
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.