CODEWITHSUNDEEP
sqlsas
vbala2014
vbala2014

Reputation: 161

SAS- Increment a numeric PK column that is auto increment within a do loop

I have to insert a PK value into a numeric column (length 8, format 23.) that is autoincrement on a destination table (tableB). I tried using the usual blank or 0 value to see if the auto increment will kick in, but it does not. I also tried using max(id)+1 and this does not work, it gives me a duplicate key error.

I have the code within a macro that does the following:

  %macro testmacro;
        %local datasetcount iter;

        proc sql;
        select count(*) into :datasetcount from tableA;

        %do %while (&iter.<=&datasetcount.);
                 data _NULL_;
                 Set tableA (firstobs=&iter. obs=&iter.);
                 run;
    PROC SQL;
       Insert into TableB(pkid, col1b, col2b, col3b)
       Select (**<need to get last pkid and increment by 1 so no duplication occurs>**, col1A, col1B, col1C)
       From tableA (firstobs=&iter. obs=iter.)
    QUIT;

    %let iter=%eval(&iter.+1);
  %end;

%mend testmacro;
%testmacro;

I just need to find a away to automatically take the last PKID entry from TABLEB and increment it by 1 and pass it into the next column until the iteration loop is done.

Upvotes: 2

Views: 1678

Answers (1)

DomPazz
DomPazz

Reputation: 12465

Use an SQL SELECT statement to get the max value from B, add 1, label the values in A, and then insert.

PROC SQL noprint:
select max(pkid) into :mpk from TableB;
quit;

data tableA;
set tableA;
pkid = _n_ + &mpk; /*_n_ is the row number*/
run;

PROC SQL noprint;
Insert into TableB(pkid, col1b, col2b, col3b)
       Select pkid, col1A, col1B, col1C
       From tableA ;
QUIT;

Upvotes: 2

Related Questions