Glad to read that my suggestion helped you.
A simple way to exclude a few variables (from the listing of distinct values) is to name them in an IF condition. For example, suppose that Height and Weight (from SASHELP.HEART) were included in our dataset HAVE. Then we could write:
data want;
set cnt;
length VALUES $300;
if upcase(name) not in ('HEIGHT' 'WEIGHT') then do;
_n_=dosubl('proc sql noprint; select distinct '||name||' into :vlist separated by ", " from have; quit;');
values=symget('vlist');
end;
else values='[too many to list]';
run;
A more elegant way, avoiding hardcoded variable names, would be to determine the number of distinct values for each variable in a preliminary step and then decide based on these numbers whether or not the distinct values of a variable should be listed. This can be done with the NLEVELS option of PROC FREQ:
/* Create test data for demonstration */
data have;
set sashelp.heart;
keep height weight smoking _char_;
run;
/* Retrieve selected metadata */
proc contents data=have out=cnt(keep=name type length varnum /* format informat */ label) noprint;
run;
/* Count distinct values of each variable */
ods select none;
ods output nlevels=nlev;
proc freq data=have nlevels;
tables _all_;
run;
ods select all;
/* Add these counts to the metadata */
proc sql;
create table cnt2 as
select c.*, NLevels from cnt c left join nlev f
on c.name=f.TableVar
order by name;
quit;
/* Add lists of unique variable values or, if there are "too many" (here: >10), just their number */
data want(drop=nlevels);
set cnt2;
length VALUES $300;
if nlevels<=10 /* or some other threshold */ then do;
_n_=dosubl('proc sql noprint; select distinct '||name||' into :vlist separated by ", " from have; quit;');
values=symget('vlist');
end;
else values=catx(' ', '[', nlevels,'distinct values ]');
run;
proc print data=want noobs;
run;
Result:
NAME TYPE LENGTH VARNUM LABEL VALUES
BP_Status 2 7 8 Blood Pressure Status High, Normal, Optimal
Chol_Status 2 10 7 Cholesterol Status , Borderline, Desirable, High
DeathCause 2 26 2 Cause of Death , Cancer, Cerebral Vascular Disease, Coronary Heart Disease, Other, Unknown
Height 1 8 4 [ 88 distinct values ]
Sex 2 6 3 Female, Male
Smoking 1 8 6 [ 15 distinct values ]
Smoking_Status 2 17 10 Smoking Status , Heavy (16-25), Light (1-5), Moderate (6-15), Non-smoker, Very Heavy (> 25)
Status 2 5 1 Alive, Dead
Weight 1 8 5 [ 178 distinct values ]
Weight_Status 2 11 9 Weight Status , Normal, Overweight, Underweight
The DOSUBL function is relatively new. It was introduced in SAS 9.4 and the original documentation (even up to release 9.4M5) was indeed difficult to grasp. Apparently it has been rewritten and extended (with more examples) recently. In practice I have basically used DOSUBL in just one project where the calculation of a mathematical function involved several DATA and PROC steps and all these steps could be executed "on the side" by means of the DOSUBL function. Thus I was able to call the complicated mathematical function in a DATA step like any other function.
Similarly, in your example the PROC SQL step creating macro variable vlist is executed "on the side" (like in a separate SAS session) in each iteration of the DATA step calling the DOSUBL function.
An introduction to the DOSUBL function with more details can be found in the SAS Global Forum 2013 paper Submitting SAS® Code On The Side by Rick Langston. Many more conference papers discussing the DOSUBL function are available in the search results https://www.lexjansen.com/search/searchresults.php?q=DOSUBL on lexjansen.com.
@anaisrossetto wrote:
Any good advice from your experience to learn SAS?
You mean: learn programming in SAS? Once you've learned the basics (e.g., in one or more of the free courses such as SAS® Programming 1: Essentials) I think the best you can do is to practice programming in SAS. Play around with DATA step statements, functions, formats, procedure options, etc. using small datasets (such as SASHELP.CLASS, SASHELP.CARS, ... or datasets you created on your own in a DATA step) and examine the logs, outputs and (using procedures like PROC PRINT and PROC CONTENTS) resulting datasets. Read the documentation of the language elements you are using and be curious to see what other functions, formats, etc. exist and what they could be used for. Personally, I never attended an introductory SAS course, but learned it (starting in 1997) on the job and from the documentation and I'm still learning by doing as well as from the documentation, from the Knowledge Base, conference papers, blogs, other online resources, books -- and by reading and writing posts in this wonderful forum.
