Hi,
I am using SAS Studio 3.6 with SAS release 9.04.01
I want to find the number of unique observations under a column in a table (distinct values) inside a macro-statement, then saving the value as a macro variable.
I found a script to find the number of observations in a table in a macro statement like below. How can I change the macro to find the number of distinct values under a column like Column_1, then save the value as a macro variable?
%macro obscnt(lib_table);
%local nobs tableid;
%let nobs=.;
* Open the data set of interest;
%let tableid = %sysfunc(open(&lib_table.));
* If the open was successful get the;
* number of observations and CLOSE &dsn;
%if &tableid %then %do;
%let nobs=%sysfunc(attrn(&tableid,nlobs));
%let rc =%sysfunc(close(&tableid));
%end;
%else %do;
%put Unable to open &tableid. - %sysfunc(sysmsg());
%end;
* Return the number of observations;
%put &nobs;
%mend obscnt;
%obscnt(table_name);
You can make @ballardw code into a macro, by replacing the table and column names with macro variables.
%macro count_distinct(var=, dset=, out=);
%global &out; *make it global to use outside of the macro;
*count distinct;
proc sql noprint;
select count (distinct age) into :&out
from &dset;
quit;
%mend;
%count_distinct(var=age, dset=sashelp.class, out=n_age);
%put &n_age;
Very cumbersome to modify that code as you would have to examine every single value and write code for tracking.
Please consider:
proc sql noprint; select count(*) into : distinctcount from (select distinct yourvariablename from yourdatasetname) ; quit;
creates a macro variable distinctcount with the count of distict values.
You can make @ballardw code into a macro, by replacing the table and column names with macro variables.
%macro count_distinct(var=, dset=, out=);
%global &out; *make it global to use outside of the macro;
*count distinct;
proc sql noprint;
select count (distinct age) into :&out
from &dset;
quit;
%mend;
%count_distinct(var=age, dset=sashelp.class, out=n_age);
%put &n_age;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.