BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
BW18
Fluorite | Level 6

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



BW18
Fluorite | Level 6
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!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 756 views
  • 1 like
  • 3 in conversation