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
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.
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;
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.
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.
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
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.
It worked! Thank you.
SAS continues to make harder operations much easier and easy operations much harder, hahaha.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: