BookmarkSubscribeRSS Feed
aasdfafafsdfsaf
Calcite | Level 5

Hi,

 

I have a library of 100+ datasets each with 10+variables. I was wondering if it was possible to output an excel table that had the table name and the column names to the right kind of like the format below. 

 

Dataset_name1 | variable_1 | variable_2 
dataset_name2 | variable_1 | variable_2| ....

 

Any help would be greatly appreciated.

Thank you.

5 REPLIES 5
Reeza
Super User

Check out sashelp.vcolumn and sashelp.vtable views in the sashelp library.

DrAbhijeetSafai
Pyrite | Level 9

That was helpful @Reeza !

 

Thank you. 

 

- Dr. Abhijeet Safai

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real
Patrick
Opal | Level 21

@DrAbhijeetSafai Just adding the bit of code required to store the result in a Excel sheet. Else just using Tom's code so suggest you accept his proposal as solution.

options dlcreatedir;
libname mylib "%sysfunc(pathname(work))\source";
data mylib.class;
  set sashelp.class;
run;
data mylib.air;
  set sashelp.air;
run;

proc contents data=mylib._all_ noprint out=contents; 
run;

proc sort data=contents;
  by libname memname varnum ;
run;

libname exl xlsx "%sysfunc(pathname(work))\myexcle.xlsx";
proc transpose data=contents out=exl.wide(drop=_name_ _label_) prefix=var;
  by libname memname ;
  id varnum;
  var name;
run;
ballardw
Super User

Something like this perhaps? You would specify your library name instead of WORK and the name stored is in uppercase so provide it that way.

proc transpose data=sashelp.vcolumn 
   out= trans  (drop=_name_ _label_)
   prefix=Varname;
   where libname='WORK';
   by memname;
   var name;
run; 

proc print data=trans;
run;

Depending on the purpose of this exercise there are other ways to use the Vcolumn data.

For example this creates a table that checks if the variables of the same name are of the same type

proc tabulate data=sashelp.vcolumn;
   where libname='WORK';
   class memname name type;
   table name*type,
         memname*n=' '
         /misstext=' '
   ;
run;

The columns are the data sets a 1 indicates that the variable name and type on the left of the row is in the data set.

If you have two types for the same variable they would be next to each other in rows to see which is where.

The n =' ' suppresses the column label of N. The Misstex=' ' table option displays no text instead of the default . for all the missing counts (variables not in the given data set column)

Tom
Super User Tom
Super User

That is what PROC CONTENTS is for.

proc contents data=mylib._all_ noprint out=contents; run;

What do you want to DO with the list?  if you would like it to appear in that strange wide format then transpose the data.

proc sort data=contents;
  by libname memname varnum ;
run;

proc transpose data=contents out=wide(drop=_name_ _label_) prefix=var;
  by libname memname ;
  id varnum;
  var name;
run;

Tom_0-1692203532793.png

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 978 views
  • 4 likes
  • 6 in conversation