Hello,   I am attempting to merge two data sets and want to use the IN= statement to create a new column that tags the data as coming from data set 1, data set 2, or both. However, I am finding that despite putting IN= followed by logic statements later in the data step to assign a value for these three conditions, only one value is being written and the "both" value gets overwritten. An example with two data sets (taken from this paper by Joshua Horstman) is attached along with code for my attempt to merge the two data sets by planet and display whether the data came from the planet_size data set, the planet_dist data set, or both. This image shows what I am getting: Note how the data_origin column only has values for 'dist', when in reality the observation for Earth should have a value of 'size' as only the planet_size data set contributed. The output that I would like to obtain is attached along with the code shown below. Any thoughts would be greatly appreciated, thanks in advance!   data work.planet_size; input planet $ diam_mi; datalines; Earth 7918 Jupiter 86881 Mars 4212 Mercury 3032 Neptune 30599 Saturn 72367 Uranus 31518 Venus 7521 ; data work.planet_dist; input planet $ dist_au; datalines; Jupiter 4.2 Mars 0.52 Mercury 0.61 Neptune 29.06 Saturn 8.54 Uranus 18.14 Venus 0.28 ; data work.planet_size_dist; merge work.planet_size(in=s) work.planet_dist(in=d); by planet; if s and not d then data_origin = 'dist'; else if d and not s then data_origin = 'size'; else if s and d then data_origin = 'dist_size'; run; /* Desired Output */ PLANET DIAM_MI DIST_AU data_origin Earth 7918 . size Jupiter 86881 4.2 dist_size Mars 4212 0.52 dist_size Mercury 3032 0.61 dist_size Neptune 30599 29.06 dist_size Saturn 72367 8.54 dist_size Uranus 31518 18.14 dist_size Venus 7521 0.28 dist_size ... View more

I have been tasked with taking the following data and creating two permanent data sets from it. One of these permanent data sets is supposed to contain the average of the "value" column for each group (meaning there should only be four rows in the end, with a new column that represents the average of respective values for A, B, C, and D). Averages should exclude missing values, meaning that if category A has a missing value, it should be divided by 3, not 4. The second permanent data set needs to be the one row with the highest overall value in the "value" column (in this case, the row with D 09JUL2021 951 should be the only row exported). I am having a tough time extracting that single row for the second data set. If you know of a way to perform these operations simultaneously, please let me know. Thank you for your time!   data work.have; input type $ date DATE9. value; datalines; A 08JUL2021 . A 09JUL2021 20 A 20JUL2021 55 A 20JUL2021 2 B 02JUL2021 9 B 22JUL2021 6 B 04JUL2021 8 B 07JUL2021 406 C 01JUL2021 215 C 28JUL2021 63 C 30JUL2021 78 C 21JUL2021 80 D 18JUL2021 951 D 09JUL2021 . D 14JUL2021 54 D 08JUL2021 73 ;   Here is what I tried:   data mylib.data1(keep=type date value value_avg) mylib.data2; set work.have; by type; if value ne . then NotMissing=1; else NotMissing=0; if first.type then call missing(of value_avg); value_avg+value; if first.type then call missing(of num_per_cat); num_per_cat+NotMissing; Avg=divide((value_avg+value),(num_per_cat+NotMissing)); if last.type then output mylib.data1; run;   This was successful for me with calculating averages, but I have no idea how to extract the row with the highest value in the "value" column to a second data set. ... View more