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
It might be that doing it manually will be quicker than creating the code for it.
After finding all your variables, you have to:
How many variables have you found in your library?
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 .
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.
Thanks , I will test it and will come with replay soon .
🙂
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;
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.
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?
Hi , I need only one sample that presents the variable includings . and I thought the the most frequented one is the most efficient way.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.