Pyrite | Level 9

## 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
1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

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

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

8 REPLIES 8
Super User

## Re: 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;``````

It's not rocket science, really.

Please state if the code above is what you intended.

Pyrite | Level 9

sure...
Opal | Level 21

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

@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;
``````
Pyrite | Level 9

## 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;

output :

a b

1 1
2 2
4 3```
Super User

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

@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;``````
Quartz | Level 8

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

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

Tourmaline | Level 20

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

``````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;``````
Pyrite | Level 9

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

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

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