Reg:Count in to a macro variable

[R_Win]

I am doing like this. data l2; input id no; cards; 1 100 2 200 3 300 run; proc sql; select count(max(no)) into :l2 from l2 where id=3; quit; i am getting this ERROR ERROR: Summary functions nested in this way are not supported. i want the output that macro L2 should have count as 1.

[R_Win]

For your Ref

[manojinpec]

You might need to use having keyword for condition of summary variables.

[Ksharp]

If I have understood what you mean.

data l2;
input id no;
cards;
1 100
2 200
3 300
run;

proc sql;
select count(*) into :l2 from l2 where id=3 having no=max(no);
quit;

%put &l2;

Ksharp

[R_Win]

small probelm data l2; input id no; cards; 1 100 2 200 3 300 3 400 run; proc sql; select count(*) into :l2 from l2 where id=3 having no=max(no); quit; %put &l2; But i want the count id =3 and no having max count here the max(no)=400 so i should get count as 1 but it was getting output as 2.

[shshiva]

Hi,

Try this...Hope it helps..

proc sql;

select count(id) as count into :s2 from l2 where id =(select max(id) from l2);

quit;

Thanks,

Shiva

[Ksharp]

It is very intesting . I don't realize it will appear so weird result. Try it.

data l2; 
input id no; 
cards; 
1 100 
2 200 
3 300 
3 400 
;
run; 
proc sql; 
select count(*)  into :l2 
 from (select *  from l2 group by id having id=3 and no=max(no) )
 ; 
quit;
%put &l2;

Ksharp