Hello all
I would like to get a list of unique values by group from a dataset. The number of groups and the number of names per group will change week by week.
For example
ID Name Group
1 M A
2 N A
3 O B
4 P B
I want to end up with the following lists:
a_names
M N
b_names
O P
What I was thinking I could do is do a proc sql to get a list of the unique groups, then iterate over that list to get lists of unique names for each group. I am able to get that first list using:
proc sql;
select unique(name) into :group_names separated by ' ' from dataset;
quit;
However, now I am stuck as to how to iterate over group_names to get a list of unique names for each. Any help is appreciated!
I did want the dynamic iteration. That link was very helpful, thank you!
Why? What are you planing to do with this list later on? For example, if this will be a parameter to a macro call it's better to leave it in a data set and use CALL EXECUTE instead.
Fully dynamic, no macros and easy to debug.
proc sql;
create table distinct_names as
select group, distinct name
from have
group by group;
quit;
*assume macro name is my_macro and takes the group and name as parameter;
data execute_macro;
set distinct_names;
str = catt('%my_macro(group=', group, ', name=', name, ');');
*this will execute the macro for each group/name;
call execute(str);
run;
I ultimately want to check if the name is valid given the group name. So, later I want to iterate over a different dataset and, if the group is A, ensure that the name is in M, N. If the name were Q, it would give an error flag.
I would still recommend keeping it in a table. Then you can easily merge to do the check more easily than looping through macro variables.
In a merge, if not in Table B then error.
proc sort data=table1; by group name;
proc sort data=table2; by group name;
data want;
merge table1 (in=a) table2(in=b);
by group name;
if a and not b then status='In Table1 Only';
else if b and not a then status='In Table 2 Only';
else status='Both Tables';
run;
Or if it's a more complex lookup, custom formats. Macro would be the last option for something like this.
Can you post an excerpt of the dataset you want to iterate over? I can't see the need to move date into macro-variables. Maybe using a hash object, dow-loops or even a format, but certainly no macro variables.
data have;
input (ID Name Group) ($);
cards;
1 M A
2 N A
3 O B
4 P B
;
data temp;
do until(last.group);
set have;
by group;
length want $ 200;
want=catx(' ',want,name);
end;
keep group want;
run;
data want;
set temp;
_want=want;
want=cats(group,'_names');output;
want=_want;output;
keep want;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.