topic Re: Compare Datasets in SAS Programming

Compare Datasets

BrahmanandaRao — Wed, 14 Jul 2021 06:42:46 GMT

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;

Re: Compare Datasets

Kurt_Bremser — Wed, 14 Jul 2021 10:36:40 GMT

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.

Re: Compare Datasets

andreas_lds — Wed, 14 Jul 2021 09:41:28 GMT

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

Re: Compare Datasets

BrahmanandaRao — Wed, 14 Jul 2021 09:50:02 GMT

Find common varables in a library for all data sets

Re: Compare Datasets

BrahmanandaRao — Wed, 14 Jul 2021 09:51:36 GMT

Find common varables in a library for all

Re: Compare Datasets

ErikLund_Jensen — Wed, 14 Jul 2021 09:54:13 GMT

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.

Re: Compare Datasets

ErikLund_Jensen — Wed, 14 Jul 2021 10:08:43 GMT

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;

Re: Compare Datasets

andreas_lds — Wed, 14 Jul 2021 10:30:29 GMT

Please show what you expect as output.

Re: Compare Datasets

Kurt_Bremser — Wed, 14 Jul 2021 10:46:35 GMT

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

Re: Compare Datasets

BrahmanandaRao — Wed, 14 Jul 2021 14:44:28 GMT

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

Re: Compare Datasets

Kurt_Bremser — Wed, 14 Jul 2021 16:33:41 GMT

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

Re: Compare Datasets

ballardw — Wed, 14 Jul 2021 17:20:52 GMT

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