topic Re: Splitting a SAS dataset into 2 sets based on specific IDs. in SAS Programming

Splitting a SAS dataset into 2 sets based on specific IDs.

BW18 — Mon, 22 Jan 2024 20:05:43 GMT

I am very new to SAS programming, and I'm working with a large dataset (Table1) that I need to split based on specific IDs, with one set containing about 70 IDs (Table2) (some with more than one row of data per ID), and the other with a few 100K. I was able to create the smaller table (Table2) with the 70 ID's, but the original set (Table1) still contains those IDs. How can I either create a new table that excludes those IDs or remove them from Table1 so they aren't duplicated when comparing the two sets? I put the code below that I used to subset the smaller table with the specific IDs. I am just nor sure how to proceed. Thanks for helping the newbie!

(Subset data into new table with specific IDs)

proc sort data=Table1;
by ID;
run;

data Table2;
set Table1;
where ID in (01, 02, 03, 04, etc...)

run;

Re: Splitting a SAS dataset into 2 sets based on specific IDs.

PaigeMiller — Mon, 22 Jan 2024 20:29:26 GMT

Let's suppose ID number 1234 and ID 5678 belongs in one data table, and the rest belong in another table.

data part1 part2;
    set table1;
    if id=1234 or id=5678 then output part1;
    else output part2;
run;

The idea of splitting this large table up this way has advantages and disadvantages. Most of the time (in my opinion) it is better to leave the table as one large table and use WHERE statements or BY statement to pick the parts you want for any particular analysis. But the answer also depends on what you plan to do with this data, what are the next steps, how will it be used — context is important and you have given us no context at all.

Re: Splitting a SAS dataset into 2 sets based on specific IDs.

yabwon — Mon, 22 Jan 2024 20:19:54 GMT

Your second step creates a new data set Table2 but only reads data from Table1, it does not modify it.

You can "overwrite" Table1, or better create Table3, with no selected IDs in the following way:

data Table2 Table3;
  set Table1;
  IF ID in (01, 02, 03, 04, etc...) 
    THEN output Table2;
    ELSE output Table3;
run;

Bart

Re: Splitting a SAS dataset into 2 sets based on specific IDs.

BW18 — Mon, 22 Jan 2024 20:51:36 GMT

Thank you so much! The If-Then-Else statement worked beautifully. I thought this might be what I needed, but I was not sure how to handle the "If" statement and whether or not each ID would have to be listed as an individual statement. Thank you for clarifying this!