data ds1 ;
set sashelp.class;
run;
data ds1;
set sashelp.class;
run;
data ds3;
set sashelp.class;
run;
proc sql ;
select * from dictionary.columns where libname='WORK' and memname ='DATA'
/*ADD THE VARIABLE LIST HERE*/
order by memname, name;
quit;
What is your question?
The SQL will successfully create a listing of the entries in the dictionary table.
Correction: if you had used MEMTYPE instead of MEMNAME. Running the SELECT with the restriction to library WORK only would have let you see which column contains the value "DATA".
But that needs using the tool between your ears.
I am not 100% sure, but maybe sas create proc compare to compare datasets.
Please show what you expect as output.
@BrahmanandaRao wrote:
Find common varables in a library for all data sets
First, get a count of the datasets, and then only the names that have that count:
data ds1;
set sashelp.class;
run;
data ds2;
set sashelp.class;
run;
data ds3;
set sashelp.class (drop=sex);
run;
proc sql noprint;
select count(*) into :dscount from dictionary.tables where libname = "WORK";
quit;
proc sql;
select name from dictionary.columns where libname = "WORK"
group by name
having count(name) = &dscount.;
quit;
Result:
Column Name Age Height Name Weight
Note that sex is absent as I removed it from ds3.
@BrahmanandaRao wrote:
Find common varables in a library for all
Here's one of my generic approaches to examining variables in multiple data sets of a library.
proc tabulate data=sashelp.vcolumn; where libname='WORK' and memtype='DATA' ; class name type memname; table name*type, memname*n=' ' /misstext=' ' ; run;
There is a 1 in the row/column intersection of variable name, type and memname (data set). I include the data type as you can see very quickly if any of the variables are of different types which is a common cause of problems when using "all the data sets in a library". You could even through in the Length into the table if you suspect that lengths change and could cause problems but that can lead to a very big table quickly.
Tabulate does have limits on table size so if you have 1000's of variables and 100's of data sets this may not complete.
You ask for a memname called DATA. To get your created data sets, try changing the select statement to:
select * from dictionary.columns where libname='WORK' and memname EQT 'DS'
This returns DS1 and DS3. You don't get a DS2, because you create DS1 twice.
If you want a list of all variables that exist in ALL the selected tables, use this select:
proc sql;
select distinct name, count(*) as antal
from dictionary.columns
where libname='WORK' and memname EQT 'DS'
group by name
having antal = (
select count(distinct memname)
from dictionary.columns
where libname='WORK' and memname EQT 'DS'
);
quit;
I didn't get why you use ds instead data ,we have memtypes namely DATA,VIEW ,CATALOG
@BrahmanandaRao wrote:
I didn't get why you use ds instead data ,we have memtypes namely DATA,VIEW ,CATALOG
Read again, and take your time to get it right. The comparison is not with MEMTYPE.
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.