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
1
2
3
3
3
4
;
run;
want
1 2
1 2
2 1
3 3
3 3
3 3
4 1
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;
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;
data want;
set data.data;
by id;
if not (first.id and last.id);
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.