topic Re: Creating number ID in sequential order in a column in New SAS User

Creating number ID in sequential order in a column

GS2 — Tue, 03 Mar 2020 14:37:59 GMT

Using SAS 9.4

I have many records and I am trying to give each record a number starting with 835 and counting up by 1 from there (836, 837, etc.). I have run the following the code:

proc sort data = controls_merged_back;
by ID;
run;
data step12;
set controls_merged_back;
keep record_id group match_key_final match_key_new2 ID Sex dob side scope_date scope_md Date_of_TKA tka_md obs4;
by ID;
if first.ID then record_id = 835;
mrn +1;
if last.ID then output;
run;

The ID variable I have is unique to each so if my thought was to start with the first ID = to 835 and count by 1 from there. However, when I run this code it produces 835 in each record, so I obviously did something wrong. If anyone has any suggestions that would be great. Thank you

Re: Creating number ID in sequential order in a column

PaigeMiller — Tue, 03 Mar 2020 14:47:52 GMT

@GS2 wrote:

Using SAS 9.4

I have many records and I am trying to give each record a number starting with 835 and counting up by 1 from there (836, 837, etc.). I have run the following the code:

proc sort data = controls_merged_back;
by ID;
run;
data step12;
set controls_merged_back;
keep record_id group match_key_final match_key_new2 ID Sex dob side scope_date scope_md Date_of_TKA tka_md obs4;
by ID;
if first.ID then record_id = 835;
mrn +1;
if last.ID then output;
run;

The ID variable I have is unique to each so if my thought was to start with the first ID = to 835 and count by 1 from there. However, when I run this code it produces 835 in each record, so I obviously did something wrong. If anyone has any suggestions that would be great. Thank you

You are not incrementing RECORD_ID anywhere. So it's always 835.

How about this:

data step12;
    set controls_merged_back;
    by ID;
    if _n_=1 then record_id = 834;
    if last.ID then do;
        record_id+1;
        output;
    end;
    keep record_id group match_key_final match_key_new2 ID Sex dob side 
        scope_date scope_md Date_of_TKA tka_md obs4;
run;

Re: Creating number ID in sequential order in a column

GS2 — Tue, 03 Mar 2020 14:50:00 GMT

That code produces 835 in the first record and 1 in the remaining records. Thank you

Re: Creating number ID in sequential order in a column

Astounding — Tue, 03 Mar 2020 16:18:39 GMT

That happens because your original data set already contains RECORD_ID with a value of 0 on every observation. The existing values of 0 throw off the calculations. You can get rid of it at the last possible moment, in simplified form:

data step12;
    set controls_merged_back (drop=record_id);
    by ID;
    retain record_id 834;
    if first.ID then record_id + 1;
    keep record_id group match_key_final match_key_new2 ID Sex dob side 
        scope_date scope_md Date_of_TKA tka_md obs4;
run;