topic Re: Start counter from last value ID in database in SAS Programming

Start counter from last value ID in database

sasboy007 — Thu, 30 Oct 2014 15:46:51 GMT

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;

Re: Start counter from last value ID in database

data_null__ — Thu, 30 Oct 2014 15:55:00 GMT

don't you need

BY X;

Re: Start counter from last value ID in database

RW9 — Thu, 30 Oct 2014 16:02:51 GMT

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;

Re: Start counter from last value ID in database

charley_sas — Thu, 30 Oct 2014 18:22:12 GMT

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;

Re: Start counter from last value ID in database

sasboy007 — Thu, 30 Oct 2014 18:42:05 GMT

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;