Compare Datasets

[BrahmanandaRao]

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;

[Kurt_Bremser]

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.

[andreas_lds]

I am not 100% sure, but maybe sas create proc compare to compare datasets.

[BrahmanandaRao]

Find common varables in a library for all data sets

[andreas_lds]

Please show what you expect as output.

[Kurt_Bremser]

---

@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]

Find common varables in a library for all

[ballardw]

---

@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.

[ErikLund_Jensen]

Hi @BrahmanandaRao 

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.

[ErikLund_Jensen]

Hi @BrahmanandaRao 

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;

[BrahmanandaRao]

I didn't get why you use ds instead data ,we have memtypes namely DATA,VIEW ,CATALOG 

[Kurt_Bremser]

---

@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.