Date:         Mon, 4 Aug 2008 10:13:54 -0400
Reply-To:     Chang Chung <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Chang Chung <chang_y_chung@HOTMAIL.COM>
Subject:      Re: Auto increment
Comments: To: Michael M <michael.muehlbacher@AON.AT>

On Mon, 4 Aug 2008 14:39:09 +0200, Michael M <michael.muehlbacher@AON.AT>
wrote:

>Hi im using SAS Enterprise Guide and SAS BASE. I need a automatic
generated Value, like the autoincrement funktion in mssql or mysql.
Supports SAS autoincrement in proc sql or in datastep?
>
>Code:
>
>Proc SQL;
>Create Tabel dummy (
>
>PK_dummy \"AutoID\"
>Date NUM INFORMAT YYMMDD10.0 FORMAT DDMMYYS10.0 not null
LABEL=\"Kontenstichtag\",
>date2                   NUM INFORMAT YYMMDD10.0 FORMAT DDMMYYS10.0 not
null LABEL=\"Programm Durchfuehrungsdatum\");
>QUIT;

hi,
i don't think there is an auto number field in proc sql. (i maybe wrong.
am i, Sig?) but it is easy to do in data step using _n_. In proc sql, I
think you can do something like below. just don't forget to update the
next.id value once you use it.
cheers,
chang

proc sql;
  /* create next table */
  create table next (id numeric);
  insert into next set id = 1;

  /* create a dummy table */
  create table dummy (id numeric primary key
    , date num format=ddmmyys10.
    , date2 num format=ddmmyys10.);

  /* whenever insert a record, get the next value
     from the next table */
  insert into dummy (id, date, date2)
  set id = (select next.id from next)
    , date='01aug2008'd
    , date2='02aug2008'd;

  /* when done inserting, update the next table */
  update next set id = id + 1;

  /* insert another */
  insert into dummy (id, date, date2)
  set id = (select next.id from next)
    , date = '03aug2008'd
    , date2 = '04aug2008'd;
  update next set id = id + 1;

  /* check */
  select * from   dummy;
quit;
/* on lst
      id        date       date2
--------------------------------
       1  01/08/2008  02/08/2008
       2  03/08/2008  04/08/2008
*/