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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.