DATA Step, Macro, Functions and more

I have data set need to count column b values how many times it repeated in column a ?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

I have data set need to count column b values how many times it repeated in column a ?

I have data set need to count column b values how many times it repeated in column a data l; input a b; cards; 1 1 2 2 2 3 3 . 3 . 3 . 3 . run; Required output : a b 1 1 2 2 4 3

Accepted Solutions
Solution
2 weeks ago
Contributor
Posts: 37

Re: I have data set need to count column b values how many times it repeated in column a

Posted in reply to KurtBremser

Hi,

 

proc sql;
create table final as
select a,
count(a) as COUNT
from l
group by a;
quit;

 

 

data ds;
set l;
by a b;
keep a count;
retain count 0;
if first.a=1 then count=1;
else count=sum(count,1);
if last.a=1 then output;
run;

Thanks,

srinath

View solution in original post


All Replies
Super User
Posts: 9,560

Re: I have data set need to count column b values how many times it repeated in column a ?

Posted in reply to rajeshalwayswel

Could you PLEASE post your code properly, like this:

data l;
input a b;
cards;
1 1
2 2
2 3
3 .
3 .
3 .
3 .
;
run;

It's not rocket science, really.

Please state if the code above is what you intended.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 81

Re: I have data set need to count column b values how many times it repeated in column a ?

Posted in reply to KurtBremser
sure...
Respected Advisor
Posts: 4,541

Re: I have data set need to count column b values how many times it repeated in column a ?

Posted in reply to rajeshalwayswel

@rajeshalwayswel

Assuming "sure...." means yes, that's how I intended to post my code.

data l;
  input a b;
  cards;
1 1
2 2
2 3
3 .
3 .
3 .
3 .
;
run;

proc sql;
  create table want as
    select a, b, count(*) as cnt
      from l
        group by a,b
  ;
quit;
Frequent Contributor
Posts: 81

I have data set need to count column b values how many times it repeated in column a

Posted in reply to rajeshalwayswel
I have data set need to count column b values how many times it repeated in column a 


data l;
input a b;
cards;
1 1
2 2
2 3
3 .
3 .
3 .
3 .
run;


output :


a b

1 1
2 2
4 3
Super User
Posts: 9,560

Re: I have data set need to count column b values how many times it repeated in column a

Posted in reply to rajeshalwayswel

@rajeshalwayswel wrote:
I have data set need to count column b values how many times it repeated in column a 


data l;
input a b;
cards;
1 1
2 2
2 3
3 .
3 .
3 .
3 .
run;


output :


a b

1 1
2 2
4 3

Try this:

proc sql;
create table want as
select count(*) as a, a as b
from l
group by a;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
2 weeks ago
Contributor
Posts: 37

Re: I have data set need to count column b values how many times it repeated in column a

Posted in reply to KurtBremser

Hi,

 

proc sql;
create table final as
select a,
count(a) as COUNT
from l
group by a;
quit;

 

 

data ds;
set l;
by a b;
keep a count;
retain count 0;
if first.a=1 then count=1;
else count=sum(count,1);
if last.a=1 then output;
run;

Thanks,

srinath

PROC Star
Posts: 1,300

Re: I have data set need to count column b values how many times it repeated in column a ?

Posted in reply to rajeshalwayswel
data have;
input a b;
cards;
1 1
2 2
2 3
3 .
3 .
3 .
3 .
run;

proc sql;
create table want as
select a,count(a) as b
from have
where a in (select b from have)
group by a;
quit;
Frequent Contributor
Posts: 81

Re: I have data set need to count column b values how many times it repeated in column a ?

Posted in reply to novinosrin

Thanks everyone.... From next time I'll post questions in proper way... Sorry for the inconvenience.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 144 views
  • 3 likes
  • 5 in conversation