DATA Step, Macro, Functions and more

How to find distinct number of rows in macro?

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

How to find distinct number of rows in macro?

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

 

 


Accepted Solutions
Solution
‎09-19-2017 03:49 PM
Super User
Posts: 22,874

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

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;

View solution in original post


All Replies
Super User
Posts: 13,084

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

[ Edited ]

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.

 

Contributor
Posts: 29

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

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.
Solution
‎09-19-2017 03:49 PM
Super User
Posts: 22,874

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

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;
Contributor
Posts: 29

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

Thanks a lot @Reeza
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 178 views
  • 4 likes
  • 3 in conversation