Counting one column according to a second column

[ziv]

Hello everyone and thanks in advance ; 

I have column A and I need column B

A                            

200

200

200

200

5

5

5

123

123

123

123

123

1

1

1

1

1

1

1

B

4

4

4

4

3

3

3

5

5

5

5

5

7

7

7

7

7

7

7

That is, I need that every time the value A changes then it will automatically count for me how many times the same value has been displayed.

[Shmuel]

It should be done in two steps; 

1) you can use proc freq to get the frequency of any value in a table

2) merge the proc freq result table with your original one

question - can values repeat like: 1 1 1 2 2 3 3 3 1 1 4 4.

would you count 3 2 3 2 2 or 5 2 3 2  ?

[Tom]

Seems simple enough.  Looks like you just want to COUNT.

First let's convert your listing into an actual dataset.

data have;
  input A @@ ;
cards;
200 200 200 200 5 5 5 123 123 123 123 123 1 1 1 1 1 1 1
;

Now we can use BY statement with the NOTSORTED option to figure out where the groups are. We can use one DO loop to count and a second to re-read the actual data and write out all of the observations for the group.

data want;
  do B=1 by 1 until(last.a);
    set have ;
    by A notsorted ;
  end;
  do until(last.a);
    set have;
    by A notsorted;
    output;
  end;
run;

Result:

OBS    B      A

  1    4    200
  2    4    200
  3    4    200
  4    4    200
  5    3      5
  6    3      5
  7    3      5
  8    5    123
  9    5    123
 10    5    123
 11    5    123
 12    5    123
 13    7      1
 14    7      1
 15    7      1
 16    7      1
 17    7      1
 18    7      1
 19    7      1

[ziv]

Hi first of all thank you very much
Do you know how to do it by chance without BY?
Because I write it inside an existing command and it doesn't accept BY on my value because it wasn't arranged before in this way.

[ziv]

If it's easier for you, then there is an option like this:

a                     b

1                     5

2                     5

3                     5

4                     5

5                     5

1                     4

2                     4

3                     4

4                     4

i have a i need b thank you!!!

[ghosh]

proc freq data=have noprint;
  table a/ nopercent nocum out=cnt(rename=(count=B) drop=percent);
run;

PROC SQL noprint;
Create table want as
  Select * from have join cnt(rename=(a=tmpa)) 
   on have.a = cnt.tmpa;
Quit;

proc print;
  var a b;
run;