DATA Step, Macro, Functions and more

Start counter from last value ID in database

Reply
Occasional Contributor
Posts: 16

Start counter from last value ID in database

Hi all,

I'm appending data to an already created table.  I'm using proc sql below to bring back the highest ID value so that I can start my new counter where it left off.  My code is below but I'm running into some issues with my IF statment at the very bottom.  I don't think the counter is actually incrementing from the last database position.  Any insight is appreciated.

/*this brings back the highest numID*/
proc sql noprint;
   select max(numID)
      into :counter 
      from gate.randomTable;

quit;

data work.newData
mycounter=0;

(keep =
mycounter
x

);

retain
mycounter
x

;

set work.dataY;

if first.x then mycounter=&counter;

      mycounter + 1;

   run;

Respected Advisor
Posts: 3,799

Re: Start counter from last value ID in database

Posted in reply to sasboy007

don't you need

BY X;

Super User
Super User
Posts: 7,942

Re: Start counter from last value ID in database

Posted in reply to sasboy007

You could also do it:

proc sql;

  create table TAB1 as

  select  MODEL,

          MONOTONIC() as NUMID

  from    SASHELP.CARS;

  create table TAB2 as

  select  MODEL,

          NUMID

  from    TAB1

  union all

  select  MODEL,

          MONOTONIC() + (select max(NUMID) from TAB1) as NUMID

  from    TAB1;

quit;

SAS Employee
Posts: 2

Re: Start counter from last value ID in database

Posted in reply to sasboy007

I'm not sure where X comes from so I removed the references to it.  Try the following:

data work.newData

retain mycounter &counter;

set work.dataY;

mycounter + 1;

<other code goes here>;

run;

Occasional Contributor
Posts: 16

Re: Start counter from last value ID in database

Posted in reply to charley_sas

Hey thanks.  I completely forgot to add &counter to my RETAIN  statement.

I ended up using and it seems to work now.

mycounter=&counter;

mycounter+1;

Ask a Question
Discussion stats
  • 4 replies
  • 289 views
  • 3 likes
  • 4 in conversation