How to list all variables in each set in a library and provide a sample

[Zeitgeist69]

Hi,

I am a new user and I need to specify my company's data base by listing all variables in each set in the library and provide a sample of the data existed in each variable (the best sample to provide is the most frequent one ) . 
Library A

Sets :

- X  has           1 , 2, 3

- Y  has           1 , 4 , 5

and Z has       2 , 9 , 8

I used this code so far , but I couldn't find out how to provide samples (using SQL) :

*List of variables in a library;


proc sql;
	create table columns as
		select libname ,
		       memname as table_name , 
		       name as variable

		from dictionary.columns
			where libname = 'A'
	;
quit;

Could you please help me with that ?
Thanks and have a nice day. KR // Alaa

[Kurt_Bremser]

It might be that doing it manually will be quicker than creating the code for it.

After finding all your variables, you have to:

• for each variable, concatenate the values from all datasets in which they appear. That requires identical attributes (type, length)
• run a proc freq on that, and select the top result

How many variables have you found in your library?

[Zeitgeist69]

Thanks for replay , The library i provided is just an example , I have actualy 10+ libraries each has 200+ data sets with 35+ variables each . 

[RW9]

As a new user, this is complicated programming, and as @Kurt_Bremser has said, probably simpler to do it by hand.  

From a purely programming aspect.

Break it into two steps:

1) Get list of all datasets/variables in the given libraries:

data list;
  set sashelp.vcolumn;
  where libname in ("ABC","DEF");
run;

In this example I have libnames of abc and def.

2) Use this list to generate code for frequencies if character, means if numeric:

proc sql;
 create table freq_out (lib char(200),ds char(200),var char(200),value char(200),count num);
quit;

data _null_;
 set list;
 if type="char" then do;
   call execute(cat('proc freq data=',catx('.',libname,memname),'; tables ',strip(name),' / out=tmp; run;'));
   call execute(cats('data tmp (keep=lib ds var value count); set tmp (rename=(',name,'=value)); length lib ds name var $200; lib="',libname,'"; ds="',memname,'"; var="',name,'"; run;'));
   call execute('proc append base=freq_out data=tmp force;run;');
 end;
 else do;
   /* Add code for proc means here */
 end;
run;

This programs uses the list dataset to generate a proc freq for each variable (so will take a while to run), and then appends the output to a main dataset, thus building up frequency counts for character variables.  I haven't added the code for means as this is only a guide.  If you don't understand this type of coding, then I would just do it manually.

[Zeitgeist69]

Thanks , I will test it and will come with replay soon . 
🙂

[s_lassen]

You can use SQL to print samples like this:

Put the code you want to execute into a macro variable, and execute that

proc sql noprint;
  select catx(' ','Title "Sample of',memname,'";select * from',cats(libname,'.', memname,'(obs=10)'))
  into :sample separated by ';'
  from dictionary.tables
  where libname='A';
  &sample;
quit;

[Zeitgeist69]

Thanks , but it displayed " No raws selected " note in the log.

[Astounding]

For your first question, this is easy.  Assuming you have defined a libname called FOLDER1:

proc contents data=folder1._all_;

run;

Getting sample values for variables is much more complex.  What do you want to do with character variables?  Would an average value for numerics be more appropriate?  I think you need to investigate this a bit before trying a complex solution.

[ballardw]

Another consideration in your "example values" display. Would you really want to display multiple values for a field that is basically open-ended text that runs 300+ characters long?

And what about variable labels?

[Zeitgeist69]

Hi , I need only one sample that presents the variable includings . and I thought the the most frequented one is the most efficient way.