Generating ID variables following a Max value on a seperate table (Proc SQL)

[xezus]

Hello! Currently using SAS Enterprise Guide 7.1

I am trying to generate numeric ID values on a new table, following the Max ID values on another table.

proc sql;
create table Bunnies as
select max(ID)as ID
from database.Rabbits;

select ID
into :ID_max
from Bunnies;

quit;

This code generate the max Id so i have a number to start with.

Its currently in to 20,000s and will continue to grow over time.

Example table(s)

ID      Name       Occupation

1        Darryl       Farmer

2        Terry         Pilot

3        Crystal      Doctor

In my new table full of newly generated information I would like for it to be

ID      Name           Occupation

4        Charles        Welder

5         Margie        Underwater Basket Weaver

6         Sarah          WNBA Player

right now the ID numbers are blank in my new table.

I appreciate any time you take into helping me solve this issue i've run into.

Thanks

[ballardw]

I am not quite sure 1) what you currently have and 2) exactly what you want for output.

Are you attempting to increment an ID variable AFTER new records are appended to your existing data set? Or do you want to add an incremented value to new data before appending to the existing data?

If you want to increment an existing id for newly appended data something like this would work:

data want;
   set have new;
   retain newid;
   if not missing(id) then newid=id;
   else do 
      id= newid +1;
      newid+1;
   end;
   drop newid;
run;

[xezus]

I currently have a table with (lets just say) 20,000 unique ID's (1 to 20,000)

I am creating a new table which will eventually be appended into the orginal, but for now I just want to generate IDs for this new table starting at ID 20,001.

I've been messing around with it from my intial post and got (I think...) one step closer.

proc sql;
update Bunnies
SET ID = &ID_max + 1;
quit;

This code has caused all my ID's in my "bunnies" table to equal my max ID from the other table + 1.

I think if I declare by ID variable in the new table as an integer before I run this, it should work?

I am trying to figure out how to do that.

[ballardw]

Show how you build the new set. It may be easier to add at that time. Also, if the order of the original data is important then I would recommend not using Proc SQL as the results are very often reordered.

[xezus]

proc sql;
create table Pre_Bunnies as
select L.*, R.*
from Pre_Bunnydummies as L
LEFT JOIN Rabbits as R
on L.C_ID = R.ID;
quit;

proc sort
data=Pre_Bunnies;
by YEAR MONTH C_ID;
run;


PROC SQL;
   CREATE TABLE WORK.NEW_Bunnies AS 
   SELECT . as C_ID, 
                    t1.NAME, 
         	  '' as YEAR,
		  '' as MONTH, 
                  '' as FOOTNOTES, 
                   t1.CHARACTERISTICS,		  
         FROM WORK.Pre_Bunnies t1;
QUIT;

This is the basic methodology for creating the new table. I needed some values saved from the old table, and some values deleted as the new values will have their own years and months. And because I didnt want to keep C_ID the same in the new table, i had to erase it and now try to generate their own unique C_ID's.

Hopefully that makes sense

[ballardw · Accepted solution]

After the SQL step to create the macro variable and that last sort step I would try:

data work.newbunnies;
   set work.prebunnies (keep=Name Characteristics);
   Id = _n_ + &ID_max;
   C_ID=.;
   /* set the lengths as actually desired below*/
   length year $ 4 month $ 10 footnotes $ 50;
run;

This will generate a message about the text variables have never been referenced but they will be created with the length as specified.

I would set the lengths to match you main dataset just to avoid any possibly issues/warning about mismatched lengths later.

[xezus]

It worked! Thank you.

SAS continues to make harder operations much easier and easy operations much harder, hahaha.

[ballardw]

I find that a number of the "harder" solutions often involve trying to use an approach more common when using different tools and trying to force SAS to behave the same.

A common example I use with people from some programming backgrounds is describe the process they would use to  find the min, max, mean and standard deviation of 50 variables.

Then show them a proc means statement of 2 lines (including the run statement). Or course all the stuff that the programmer using other languages is in the background but that's why we use a high level program, to avoid routine and tedious as much as practical.