topic Re: Getting a single value for each group in values ties situation using proc SQL in SAS Procedures

Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Sat, 04 Aug 2018 14:26:33 GMT

I stuck in a situation in an example:

data xyz;
input ID:$6.  Symbol:$2. @@;
datalines;
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
;
run;
proc sql feedback;
select *, count(ID) as Total from Work.xyz group by Symbol, ID;
quit;

OUTPUT:

Output of above select query.

Instead of the above output, I want only this one as output:

For each group, I want only a single row when values of total column gets tied.

Note: I need a solution for 1 million data, above is just an example. Total value can be different.

Someone help me out.

Re: Getting a single value for each group in values ties situation using proc SQL

PaigeMiller — Sat, 04 Aug 2018 13:49:59 GMT

It's impossible to provide a generalized solution without more information.

Are the 2nd symbol values always mirror images of the first symbol value (example: UV and VU)?

Can there be more than one mirror image pair of symbols in a given ID? (For example, can ABC104 have UV and VU and BG and GB and TR and RT?)

Are the letters in the symbols always adjacent to one another in the alphabet? (V is adjacent to U, but can V appear with A?)

Does this solution HAVE to be in SQL? Because even if you say "YES", I would likely provide a non-SQL solution if I think of one.

Re: Getting a single value for each group in values ties situation using proc SQL

novinosrin — Sat, 04 Aug 2018 13:57:12 GMT

Diagnose it. That is because you have the following distinct combinations

proc sql ;
select distinct id, symbol from xyz;
*select *, count(ID) as Total from Work.xyz group by Symbol, ID;
quit;

ID Symbol
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
ABC101 XY
ABC101 YX
ABC102 PQ
ABC102 QP
ABC103 IJ
ABC103 JI
ABC104 UV
ABC104 VU

So you basically have 8 different groups as a combination of id and symbol

Re: Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Sat, 04 Aug 2018 14:17:22 GMT

@Paige Miller
No, letters in the symbol are not adjacent. They're random in nature. The combination can be any within 26 alphabets and alphanumeric.

I would like to prefer any other non-SQL if there is no solution for SQL.
Provide me the solution, Miller.

Re: Getting a single value for each group in values ties situation using proc SQL

Astounding — Sat, 04 Aug 2018 14:17:40 GMT

I think SQL permits SUBSTR. If so, add to the SELECT statement:

where substr(symbol, 1, 1) <= substr(symbol, 2, 1)

Re: Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Sat, 04 Aug 2018 14:24:32 GMT

@novinosrin

I know that there are 8 different groups as a combination of id and symbol. But I want only topmost count(Total) value for each id and if count values get tied in each group then it returns only top row i.e. 1st one.

Re: Getting a single value for each group in values ties situation using proc SQL

novinosrin — Sat, 04 Aug 2018 16:30:14 GMT

data xyz;
input ID:$6.  Symbol:$2. @@;
datalines;
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 VU
;
run;
/*i added one more row to test for non ties ABC104 VU */


/*if count values get tied in each group then it returns only top row i.e. 1st one,
since proc sql order is not guaranteed,creating a counter column n to the dataset "as is".*/
data temp;
set xyz;
by id;
if first.id then n=1;
else n+1;
run;

proc sql;
create table want(drop=n f) as
select *, case when std(total)=0 then  min(n) else max(total) end as f
from
(select *, count(ID) as Total from Work.temp group by Symbol, ID	
having n=min(n))
group by id
having (n=f) or (f=total);
quit;

Re: Getting a single value for each group in values ties situation using proc SQL

art297 — Sat, 04 Aug 2018 16:40:16 GMT

I also added an extra record and, with my test data, @novinosrin's code didn't produce the desired result. The following requires an extra pass of the data, but with only a million records, shouldn't take very long:

data xyz;
  input ID:$6.  Symbol:$2. @@;
  datalines;
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
;
run;

data for4mat (drop=i j k);
  retain fmtname 'symbols' type 'C';
  length start $2;
  length label $2;
  array astart(36) $2 _temporary_;
  do i=65 to 90,48 to 57;
    j+1;
    astart(j)=byte(i);
  end;
  k=0;
  do i=1 to 36;
    do j=i+1 to 36;
      start=catt(astart(i),astart(j));
      label=catt(astart(i),astart(j));
      output;
      start=catt(astart(j),astart(i));
      label=catt(astart(i),astart(j));
      output;
    end;
  end;
run;

proc format cntlin = for4mat;
run;

data need;
  set xyz;
  length symbol2 $2;
  symbol2=put(symbol,$symbols.);
run;

data need (drop=symbol2 test);
  set need;
  by id symbol2 notsorted;
  length test $2;
  retain test;
  if first.symbol2 then test=symbol;
  if symbol eq test;
run;
  
proc sql;
  create table want as
    select distinct *, count(ID) as Total
      from need
        group by ID, Symbol
  ;
quit;

Art, CEO, AnalystFinder.com

Re: Getting a single value for each group in values ties situation using proc SQL

novinosrin — Sat, 04 Aug 2018 17:20:27 GMT

Thank you @art297 and sorry to bother with a request on a weekend, would a tweak

having (n=f) or (f=total and n=min(n)); suffice?

EDITED after some serious test: Won't suffice if there is another combination at n=1.. Pardon me.Sorry about that

Re: Getting a single value for each group in values ties situation using proc SQL

PaigeMiller — Sat, 04 Aug 2018 17:15:09 GMT

@Saurabh291989 wrote:
@Paige Miller
No, letters in the symbol are not adjacent. They're random in nature. The combination can be any within 26 alphabets and alphanumeric.

So I asked you to clarify four different points regarding your data, and you clarified one of the four.

Re: Getting a single value for each group in values ties situation using proc SQL

novinosrin — Sat, 04 Aug 2018 23:55:45 GMT

data xyz;
  input ID:$6.  Symbol:$2. @@;
  datalines;
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
;
run;

data want;
 
 if _n_ = 1 then do;
   declare hash h();
   h.defineKey('id','symbol');
   h.definedata('count');
   h.defineDone();
 end;

do until (l);
   set xyz end=l;
  if h.find() ne 0 then do;count=1;h.replace();end;
  else do;count=count+1;h.replace();end;
end; 
l=0;
do until(l);
	m=.;
	do until(last.id);
		set xyz end=l;
		by id;
		rc=h.find();
 		m=max(count,m);
	end; 
		f=0;
	do until(last.id);
		set xyz ;
		by id;	
		rc=h.find();
		if m=count and  not f then do; output;f=1;end;
	end;
end;
keep id symbol count;
run;

Re: Getting a single value for each group in values ties situation using proc SQL

art297 — Sun, 05 Aug 2018 00:43:56 GMT

@novinosrin: Your code needs an adjustment as it's losing the fifth record, i.e.: ABC101 QP

Interestingly, on the small test file, my approach runs faster. Be interesting to see how the two approaches compare on a million record file.

Art, CEO, AnalystFinder.com

Re: Getting a single value for each group in values ties situation using proc SQL

novinosrin — Sun, 05 Aug 2018 01:07:39 GMT

Good evening @art297 my understanding is that you can't have duplicate ID in your result and the code is supposed to check for max of counts of combination of groups and pick the earliest group combination within an ID that has max of the counts. Your code is picking two different counts for the same ID ABC101. I am not sure if that is what is desired.

Basically,if my understanding is correct , the algorithm would have to check for

1. compute the counts with combination of id, symbol group by

2. get the max of counts within a group id and not combination

3. if no ties, easy pick that else pick the earliest combination that satisfies the max count

ABC101 QP has a count of 1 whilst other ABC101 has greater counts. So i think that anyway doesn't meet the max count need.

It always makes me nervous to to be part of thread where champs like you are part of. Sorry if any of the above doesn't make sense

Re: Getting a single value for each group in values ties situation using proc SQL

art297 — Sun, 05 Aug 2018 01:32:06 GMT

@novinosrin: Makes perfect sense! Either you're right, I am, or neither of us are.

Don't be nervous! You have picked up so much in the short time you've been using/learning SAS that one would be hard pressed to distinguish your posts from any of the old timers. In fact, your code has often been the best proposed.

Art, CEO, AnalystFinder.com

Re: Getting a single value for each group in values ties situation using proc SQL

Ksharp — Sun, 05 Aug 2018 11:25:19 GMT

Is it that necessary to use SQL ?

data xyz;
input ID:$6.  Symbol:$2. @@;
datalines;
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
;
run;
proc sql feedback number;
select * from
(select *, count(ID) as Total from Work.xyz group by Symbol, ID)
group by id
having symbol=min(symbol);
quit;

Re: Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Mon, 06 Aug 2018 11:33:29 GMT

@Paige Miller:
Apology for the delay:

1) No, 2nd symbol value is not the mirror images of the 1st symbol.
2) Yes, there are approx. 100-1000k mirror image pair of symbols in a given ID.
3) Nope, the letters in the symbols always not adjacent to one another in the alphabet.
4) Actually, this query has to resolve with the proc SQL but I'll take the chance with the Non-SQL solution.

Re: Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Mon, 06 Aug 2018 11:35:34 GMT

@Astounding:

Sorry, buddy! it didn't work.

Re: Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Mon, 06 Aug 2018 12:11:42 GMT

@novinosrin:
Yes, @novinosrin, you understood my problem correctly.

Your quotes:

" Basically,if my understanding is correct , the algorithm would have to check for

1. compute the counts with combination of id, symbol group by

2. get the max of counts within a group id and not combination

3. if no ties, easy pick that else pick the earliest combination that satisfies the max count"

That's what I'm looking for.

Re: Getting a single value for each group in values ties situation using proc SQL

art297 — Mon, 06 Aug 2018 13:28:29 GMT

Just have one question: given the following example dataset, what result would you expect for ID=ABC101?

data xyz;
  input ID:$6.  Symbol:$2. @@;
  datalines;
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 XY ABC101 YX
ABC101 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC102 PQ ABC102 QP
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC103 IJ ABC103 JI
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
ABC104 UV ABC104 VU
;
run;

Art, CEO, AnalystFinder.com

Re: Getting a single value for each group in values ties situation using proc SQL

Saurabh291989 — Mon, 06 Aug 2018 13:56:27 GMT

@ art297:
Actually, I'm looking for the topmost value for the ABC101 i.e. it doesn't matter whether it is XY, YX or QP. Only matters, it should be maxed in the count and if a group count values tie, it selects only the 1st value.
example:
ID SYMBOL COUNT
ABC101 XY 4
ABC101 PQ 4
ABC101 YX 4
ABC101 AB 4

if this situation arrived only top value showed up in the result i.e. ABC101 XY 4