topic Re: How to create a list of unique values by group? in SAS Programming

How to create a list of unique values by group?

meriS — Fri, 29 Jul 2022 21:19:43 GMT

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!

Re: How to create a list of unique values by group?

pink_poodle — Fri, 29 Jul 2022 21:51:19 GMT

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-perform-a-do-loop-over-a-list-of-values/

Re: How to create a list of unique values by group?

meriS — Fri, 29 Jul 2022 22:13:05 GMT

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

Re: How to create a list of unique values by group?

Reeza — Fri, 29 Jul 2022 22:22:30 GMT

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;

Re: How to create a list of unique values by group?

meriS — Fri, 29 Jul 2022 22:26:15 GMT

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.

Re: How to create a list of unique values by group?

Reeza — Fri, 29 Jul 2022 22:36:18 GMT

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.

Re: How to create a list of unique values by group?

Ksharp — Sat, 30 Jul 2022 10:24:54 GMT

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;

Re: How to create a list of unique values by group?

andreas_lds — Mon, 01 Aug 2022 05:56:01 GMT

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.