topic Re: Counting one column according to a second column in SAS Programming

Counting one column according to a second column

ziv — Sun, 05 Feb 2023 12:16:37 GMT

Hello everyone and thanks in advance ;

I have column A and I need column B

200

123

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.

Re: Counting one column according to a second column

Shmuel — Sun, 05 Feb 2023 14:26:40 GMT

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 ?

Re: Counting one column according to a second column

Tom — Sun, 05 Feb 2023 15:57:05 GMT

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

Re: Counting one column according to a second column

ghosh — Sun, 05 Feb 2023 19:38:56 GMT

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;

Re: Counting one column according to a second column

ziv — Thu, 09 Feb 2023 14:55:29 GMT

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.

Re: Counting one column according to a second column

ziv — Thu, 09 Feb 2023 15:54:34 GMT

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!!!