topic count by ID - keep each row in SAS Programming

count by ID - keep each row

jbrau123 — Sun, 17 Jul 2022 17:14:53 GMT

Dear forum

I am trying to figure out how to simply count number of rows by ID whilst keeping each row, since I want to afterwards delete observations that only have one row. Therefore I don't want just one row that counts ID.

I have tried to explain below. It should be fairly simple, and I tried to do it with a PROC SQL step, but it didn't work. Any help would be highly appreciated.

Kind regards,

Jacob

What I tried, that did not work:

PROC SQL;
SELECT ID
,COUNT([ID]) OVER (PARTITION BY [ID]) AS NrObs
INTO data.data2
FROM data.data
;
QUIT;
RUN;

_______________________________

Have:

data data;
input ID;
datalines;
1

;
run;

want

1 2

2 1

3 3

4 1

Re: count by ID - keep each row

ghosh — Sun, 17 Jul 2022 17:32:12 GMT

data one;
  set data;
    by id;
    if first.id then n=1;
      else n+1;
    if last.id;
run;
data want;
   merge data one;
     by id;
run;

Re: count by ID - keep each row

PaigeMiller — Sun, 17 Jul 2022 17:37:15 GMT

data want;
    set data.data;
    by id;
    if not (first.id and last.id);
run;

Re: count by ID - keep each row

jbrau123 — Mon, 18 Jul 2022 01:21:21 GMT

Thank you very much!