Create a dataset indicating the variables present in multiple datasets

[SASuserlot]

I want to create a dataset like an image. It checks multiple datasets and gives information about which dataset has which variable. This dataset I eventually use it to export to keep track of variables present in all datasets. How can I achieve this? For this purpose, I am only giving two datasets, but these can sometimes be 20- 30 datasets. Thank you for your suggestions and tips

 data have1;
 age = 12;
 name = "Ram";
 height =170;
 run;

 data have2;
 age = 12;
 name = "Ram";
 weight =156;
 run;

[PaigeMiller]

proc sql;
    create table columns as select memname,name
    from sashelp.vcolumn
    where libname='WORK' and memname in ('HAVE1','HAVE2');
quit;
proc transpose data=columns out=columns_t(drop=_name_ _label_);
    by memname;
    var name;
    id name;
run;

Not exactly what you asked for, but if you absolutely must have 'X' in the cells (why?), a DATA step can fix that.

[SASuserlot]

Thanks, @PaigeMiller. It will do the job. I am keeping "X' to filter the column in Excel sheet but not mandatory. This will do the job; I just modified your code a little bit and used 'proc contents' ( with the help of a Friend) instead of the 'vcolumn' dataset. But the approach will be more efficient than mine.

 data have1;
 age = 12;
 name = "Ram";
 height =170;
 run;

 data have2;
 age = 12;
 name = "Ram";
 weight =156;
 run;

proc contents data= have1 out=chave1;
run;

proc contents data= have2 out=chave2;
run;
data chave3;
set chave1 chave2;
col = 'x';
run;

proc transpose data= chave3 out = thave3;
by memname;
var col;
id name;
run;

data want;
set have1 have2 indsname =b;
tblnmae1=b;
run;

[Reeza]

https://gist.github.com/statgeek/3b57ae085d9f7a36a2d95c15f04e72e6

[SASuserlot]

I appreciate the reference. Is it mandatory to have the account( is it paid membership) to access the link?

[Reeza]

No.

[Kurt_Bremser]

With DATA step as first step, the "X" is simple:

data columns;
set sashelp.vcolumn; 
where libname = 'WORK' and memname in ('HAVE1','HAVE2');
v = "X";
keep memname name v;
run;

proc transpose data=columns out=columns_t(drop=_name_ _label_);
by memname;
var v;
id name;
run;

[ballardw · Accepted solution]

You might consider something like this to generate a report.

proc format;
value Myx
1='X'
other=' '
;
run;

proc tabulate data=sashelp.vcolumn;
   where libname='SASHELP' and memtype='DATA';
   class memname name;
   table memname,
         name*n=' '*f=myx. 
   ;
run;

The format is to place X in the report.

The SASHELP.Vcolumn view contains details of all the variables in all the datasets (memname) in all of the libraries currently assigned. The LIBNAME and MEMNAME are stored in uppercase when you want to filter.

If you only want specific data sets you could add a list such as: Memname in ('HAVE1' 'HAVE2') but if the interest is looking at many sets then the whole library approach may be preferred.

If I were going to have more variables (very likely) than data sets I recommend having the Name (variables) as the row and the data set (memname) as the columns. If you have 5 data sets with 500 variables the wide view is going to be frustrating.

I might also suggest including the variable TYPE so you could quickly identify the variables with the same name that have different types. That way you don't get surprised by messages about incompatible types

proc tabulate data=sashelp.vcolumn;
   where libname='YOURLIB' and memtype='DATA';
   class memname name type;
   table name*type,
         memname*n=' '*f=myx. 
   ;
run;

The SASHELP.VCOLUMN also has the bits about variable length, assigned format, informat, label, use in an index or sorted, the column position in the set so it is pretty easy to add such information to the report.

[SASuserlot]

 I wish I could 'PROC EXPORT" each of your brains into my head😀.  @ballardw @A_Kh @Kurt_Bremser @PaigeMiller @Reeza  thank you all for your suggestions and alternative ways to achieve this. Unfortunately, I can select one as "Answer". Each of your solutions works but I guarantee you guys covered most of anybody's requirements in this concept in the future. I really appreciate all of you guys.

[A_Kh]

Another way is to use dictionary.columns. Advantage - you don't need to specify dataset names, only LIBRARY name is enough. It will give you all dataset names (in the directory) with the same variables. 

eg:

proc sql;
	select name 'Variable_Name', count(memname) 'Count', memname 'Dataset_name'
		from dictionary.columns
			where libname eq 'WORK'
		group by 1
       order by 1;
quit; 

output: