Insert new column with iterative value to every unique rows in a table using proc sql

May I know if there's technique to insert new column with iterative value to every unique rows in a table?

Example:

TABLE HAVE

ID  name
1   John
2   Matt
3   Pete

Now, I have a counter = 3 and I want to add every counter value up to 3 to each unique ID in table HAVE.

TABLE WANT

ID name count
1  John 1
1  John 2
1  John 3
2  Matt 1
2  Matt 2
2  Matt 3
3  Pete 1
3  Pete 2
3  Pete 3

I can do this using data step using combination of by and first.var:

data want;
  set have;
    by ID;
  if first.ID then do;
    do i = 1 to count;
      output;
    end;
  end;
run;

My main issue here is the runtime, data step process a data set sequentially and may take some time to run. I want to know if this can be done using proc sql?

Solution

It cannot be done particularly easily proc sql using built-in functionality. One solution is if you have a tally or number table of some sort. Then you could just do:

select id, t.name, n.n
from t join
     numbers n
     on n.n <= :counter;

In fact, if your ids are sequential with no gaps (as in your example), you could use a self join:

select t.id, t.name, n.id as count
from t join
     t n
     on n.id <= :counter;

If you know the specific value, you could construct a union all query:

select id, name, 1 as count from t
union all
select id, name, 2 as count from t
union all
select id, name, 3 as count from t;

Modern SQL now has constructs that simplify this process (window functions and recursive CTEs for instance). However, those are not available directly in proc sql.