Suppose you want a list of car manufacturers from the CARS dataset. Easy! Call the %CHARLIST macro from a %PUT statement, like this:
The CHARLIST macro generates a list of unique values of a selected variable from a selected dataset. So does PROC FREQ. But, if you don't need statistics, the CHARLIST macro is quick and easy to call, and can be called from anywhere.
Suppose you want a list of all available vehicle types to appear in a footnote. Easy! Call the %CHARLIST macro from a FOOTNOTE statement, like this:
proc print data=sashelp.cars(obs=10) noobs; var make model type msrp; title "First 10 rows from CARS dataset"; footnote "Vehicle types include: %charlist(sashelp.cars,type)"; run;
You can't call PROC FREQ (or any proc) from a FOOTNOTE statement!
The CHARLIST macro:
%macro charlist(dsn,var) / minoperator mindelimiter=','; %local dsid rc list i; %let dsid=%sysfunc(open(&dsn(keep=&var))); %syscall set(dsid); %let rc=%sysfunc(fetch(&dsid)); %let list=&&&var; %do i=2 %to %sysfunc(attrn(&dsid,nlobs)); %let rc=%sysfunc(fetch(&dsid)); %if not (%bquote(&&&var) in &list) %then %let list=&list, %bquote(%trim(&&&var)); %end; %let dsid=%sysfunc(close(&dsid)); &list %mend charlist;
- The OPEN function opens SASHELP.CARS, keeping only the variable MAKE.
- The SET routine creates a macro variable MAKE from the dataset variable MAKE.
- The NLOBS argument of the ATTRN function returns the number of observations in a dataset, which serves as the stop value of the %DO loop.
- The FETCH function reads observations from SASHELP.CARS, populating the macro variable MAKE.
- If the current MAKE is not already in the macro variable LIST, it is appended to LIST.
Be sure to save the CHARLIST macro in your stored compiled macro or autocall library for convenient re-use!
The CARFINDER macro calls the CHARLIST macro. The CARFINDER macro accepts a CAR parameter (vehicle make) which is referenced in the WHERE and TITLE statements. More importantly, the CAR parameter is validated against a list of all car makes in the SASHELP.CARS dataset, to insure a valid car make is selected. If so, the CARFINDER macro issues a PROC PRINT. If a valid car make is not selected, the CARFINDER macro issues an error message, followed by a list of valid car makes to choose from. This is called data-driven parameter validation.
%macro carfinder(car) / minoperator mindelimiter=','; %if &car in %charlist(sashelp.cars,make) %then %do; proc print data=sashelp.cars noobs; var model type msrp; where make="&car"; title "&car"; run; %end; %else %do; %put ERROR: No &car.s.; %put NOTE: Cars include %charlist(sashelp.cars,make).; %end; %mend carfinder;
Did you spot the inefficiency? The CARFINDER macro is inefficient because it potentially calls the CHARLIST macro twice, which then reads the CARS dataset twice. Avoiding this inefficiency is easy. Call the CARFINDER macro only once, from a %LET statement, to create the macro variable CARMAKES, which stores the list of valid car makes. Then reference the macro variable CARMAKES as needed.
%macro carfinder(car) / minoperator mindelimiter=','; %local carmakes; %let carmakes=%charlist(sashelp.cars,make); %if &car in &carmakes %then %do; proc print data=sashelp.cars noobs; var model type msrp; where make="&car"; title "&car"; run; %end; %else %do; %put ERROR: No &car.s.; %put NOTE: Cars include &carmakes.; %end; %mend carfinder;
Happy car finding!