In this example, Alice is overwritten again with the previous Alice. So what happens to the row containing Barbara? Does Barbara make it to the final table?   ... View more

For example, I have a data set, with column name school subjects students are interested in. The old column would look like this:   School Subjects: Health science and Math Biology Biology and Geology Arts and Computer Science Geology and Health Science Math   My goal, is to extract the second school subject (ie Math or Geology) and put it in a new column to create the desired result:   School Subject 1                 School Subject 2 Health Science                      Math Biology Biology                                  Geology Arts                                       Computer Science Geology                                Health Science Math   My solution was to create 2 conversion types, based on "and" and use if and then statement to separate the subjects: Conversiontype1 = scan(school_subjects, -2, " "); Conversiontype2 = scan(school_subjects, 2, " "); If conversiontype1 = "and" then school_subject1 = scan(school_subjects, -1, " "); Else if conversiontype2 = "and" then school_subject2 = catx(" ", scan(school_subjects, -2), scan(school_subjects, -1)); else school_subject1 = school_subjects; run;    Is there a simpler way of doing this? ... View more

I've seen so many different combinations of drop/keep statement, that my head is spinning. I need to understand the drop/keep statement and how to effectively use it. I understand the drop/keep after or in the data statement or at the end is the same thing, just a matter of processing time. Everything else needs clarity especially how compile time affects this.    For this example in the picture, SAS reads the 8 columns from the input table(customer) then creates two columns in the output table(Canada & US) and then drops the other columns(state & zip)? If the two columns(state & zip) will be dropped at the beginning from the data step, why include it in the set statement?   For the drop country listed at the bottom, does it make a difference if dropped earlier or at the data statement?   I've seen the drop country added several ways and still used in the code to process other commands(like below). So what is the difference? data Canada; set customer; drop country; if country = "Canada" then output Canada; else if country = "US" then output us; run; data Canada; drop country; set customer; if country = "Canada" then output Canada; else if country = "US" then output us; run; Any help is appreciated.    ... View more

I have two datasets that I would like to merge together, but each datasets have a series of columns that need to be sorted, renamed or trimmed. I have a column "top" that repeats several times with additional numbers and I'm trying to crop out the following sequential characters or extract just "to" from all columns itself to later remove it. My goal is to remove the character "to" from appearing in the final data, but I tried doing that directly and it did not work.    For example, Dataset 1 has "  Top1 Top2 Top4....   Dataset 2 has:  Tops23 Tops45 Topsi46...   Here are some codes I was thinking of using:   Proc sort data = first_dataset ; by id; run;   data first_dataset_clean; set first_dataset ; by id; to = substr(ex:, 1, 3); run;   data first_dataset_clean second_dataset_clean; merge first_dataset(rename = ("top:" = "to")) second_dataset (rename = ("top:" = "to")) by id; if first.id = 1 and last.id =1 then output first_dataset_clean; else output second_dataset_clean; run;   I've been considering using "where ne", "scan", "tranwrd", "substr", "rename" but couldn't figure out how to pull out just "to" from both datasets.    Any thoughts?   ... View more