sas

Reply
Contributor
Posts: 59

sas

data test;

input id$ card$ amnt;

cards;

1 123 10

1 123 23

1 234 22

1 234 54

1 232 43

.....

 

 

;run;

i want the card with max no of transactons.


proc sql;
select * ,count(*) as c from test group by cust,debitcard having c=max(c);

I'm running it and getting error that Summary functions nested in this way are not supported??

Super Contributor
Posts: 490

Re: sas

[ Edited ]

Summary functions can not be nested like what you did. As you can see in the log

ERROR: Summary functions nested in this way are not supported. 

 

Instead, if you still want to use PROC SQL

(1) Break it to two query like this 

proc sql;
CREATE TABLE numcard AS 
select card, count(*) as c
from test 
group by card
;
CREATE TABLE mostcard AS
SELECT * FROM numcard
HAVING c = MAX(c) ;
QUIT; 

or

(2) Use nested queries like this 

PROC SQL;
 CREATE TABLE mostcard AS
 SELECT *
 FROM
 (SELECT card, count(*) as c
 FROM test
 GROUP BY card)
HAVING c = MAX(c) ;
QUIT;

 

Super Contributor
Posts: 336

Re: sas

Try:

 

data test;
input id$ card$ amnt;
cards;
1 123 10
1 123 23
1 234 22
1 234 54
1 232 43
1 234 12
;run;

Proc SQL;
  Select card, Max(C_Count) As Max_C_Count
  From 
  (
  Select card, Count(Card) As C_Count From test Group By card 
  )
  Having C_Count=Max(C_Count);
Quit;
Super Contributor
Posts: 336

Re: sas

Mohamed_zaki was quicker :-)
Trusted Advisor
Posts: 1,203

Re: sas

proc sort data=test;
by card;
run;

 

data want(keep=card max_txns);
set test end=last;
max_txns=0;
by card;
if first.card then cnt=0;
cnt+1;
if cnt>max_txns then max_txns=cnt;
if last;
run;

Ask a Question
Discussion stats
  • 4 replies
  • 334 views
  • 0 likes
  • 4 in conversation