topic Re: Assign id in SAS Procedures

Assign id

Miracle — Wed, 18 Aug 2021 09:15:11 GMT

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

Re: Assign id

Kurt_Bremser — Wed, 18 Aug 2021 09:23:03 GMT

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.

Re: Assign id

Miracle — Fri, 20 Aug 2021 08:49:55 GMT

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.

Re: Assign id

PaigeMiller — Fri, 20 Aug 2021 11:05:46 GMT

@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.

Re: Assign id

Kurt_Bremser — Fri, 20 Aug 2021 14:17:28 GMT

@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.

Re: Assign id

Miracle — Wed, 25 Aug 2021 16:42:36 GMT

Thanks everyone for the advice.