BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cercig
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
ballardw
Super User

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.

 

cercig
Obsidian | Level 7
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.
Reeza
Super User

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;
cercig
Obsidian | Level 7
Thanks a lot @Reeza

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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