topic Re: How to list all variables in each set in a library and provide a sample in SAS Programming

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

Zeitgeist69 — Fri, 31 Aug 2018 14:18:33 GMT

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

Re: Who to list all variables in each set in a library and provide a sample

Kurt_Bremser — Fri, 31 Aug 2018 08:26:46 GMT

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?

Re: Who to list all variables in each set in a library and provide a sample

Zeitgeist69 — Fri, 31 Aug 2018 08:35:56 GMT

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 .

Re: Who to list all variables in each set in a library and provide a sample

RW9 — Fri, 31 Aug 2018 08:59:24 GMT

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.

Re: Who to list all variables in each set in a library and provide a sample

Zeitgeist69 — Fri, 31 Aug 2018 09:25:22 GMT

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

Re: Who to list all variables in each set in a library and provide a sample

s_lassen — Fri, 31 Aug 2018 13:18:10 GMT

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;

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

Astounding — Fri, 31 Aug 2018 20:20:09 GMT

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.

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

ballardw — Fri, 31 Aug 2018 22:13:35 GMT

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?

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

Zeitgeist69 — Mon, 03 Sep 2018 13:16:22 GMT

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

Re: Who to list all variables in each set in a library and provide a sample

Zeitgeist69 — Mon, 03 Sep 2018 13:18:46 GMT

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