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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 4287 views
  • 1 like
  • 3 in conversation