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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: