How to create a list of unique values by group?

[meriS]

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!

[pink_poodle]

Keep going:
Select unique(name) into :a_names separated by “ “
From dataset
Group by group
Having group = “A”
;
If you want this to be dynamic, then
1) select unique(group)
Into &groups separated by “ “
;
2) iterate over &groups like so:
https://blogs.sas.com/content/sastraining/2015/01/30/sas-authors-tip-getting-the-macro-language-to-p...

[meriS]

I did want the dynamic iteration. That link was very helpful, thank you!

[Reeza]

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;

[meriS]

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. 

[Reeza]

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. 

[andreas_lds]

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.

[Ksharp]

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;