BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
jbrau123
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ghosh
Barite | Level 11
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;

ghosh_0-1658079123656.png

 

View solution in original post

3 REPLIES 3
ghosh
Barite | Level 11
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;

ghosh_0-1658079123656.png

 

jbrau123
Obsidian | Level 7
Thank you very much!
PaigeMiller
Diamond | Level 26
data want;
    set data.data;
    by id;
    if not (first.id and last.id);
run;
--
Paige Miller

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 4223 views
  • 1 like
  • 3 in conversation