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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.