topic Re: How to find distinct number of rows in macro? in SAS Programming

How to find distinct number of rows in macro?

cercig — Tue, 19 Sep 2017 18:58:27 GMT

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);

Re: How to find distinct number of rows in macro?

ballardw — Tue, 19 Sep 2017 20:41:09 GMT

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.

Re: How to find distinct number of rows in macro?

cercig — Tue, 19 Sep 2017 19:12:53 GMT

Hi @ballardw
I want to do the counting inside a macro-statement like %macro; and %mend;
Because I want to use the same macro for different tables repeatedly.
Or maybe it is easier to do the counting at the end of every code.

Re: How to find distinct number of rows in macro?

Reeza — Tue, 19 Sep 2017 19:33:13 GMT

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;

Re: How to find distinct number of rows in macro?

cercig — Tue, 19 Sep 2017 19:49:54 GMT

Thanks a lot @Reeza