Assign id

[Miracle]

Dear All,

May I know how do go from 'have' to 'want' using proc sql?

Thanks a lot.

have
studyid test
abc110  1
abc110  2
abc110  3
abc201  1
abc201  2
abc405  1
abc405  2

want
studyid test   n
abc110  1           1
abc110  2          1
abc110  3          1
abc201  1          2
abc201  2         2
abc405  1         3
abc405  2         3

[Kurt_Bremser]

The task defines the tool, so you use the data step for this:

data want;
set have;
by studyid;
if first.studyid then n + 1;
run;

No need to code yourself to insanity when there's such a simple solution.

[Miracle]

Thanks @Kurt_Bremser  for your solution using datastep.
But I need to perform this manipulation using proc sql.
BTW, this is not homework. I try to use proc sql more for my work now.
Thanks a lot.

[PaigeMiller]

---

@Miracle wrote:

But I need to perform this manipulation using proc sql.

---

Really a bad idea. Use a DATA step. Don't listen to me, listen to @Kurt_Bremser , he knows.

[Kurt_Bremser]

---

@Miracle wrote:

I try to use proc sql more for my work now.

---

Mastery of a tool also means knowing when not to use it.

SQL is very useful for a lot of things, but using it here, and in a production environment(!), would simply be stupid. The next one to maintain that code will come after you with murder in their eyes.

[Miracle]

Thanks everyone for the advice.