Hello,  I am trying to remove duplicates from a large dataset and have been having trouble doing that. I was using proc sql to do it but it seems like it does it for one criteria but not the other. Specifically, it will work for the max test_scores but it won't work for " test_scores is null and Pending = 'YES'"  . I did this proc sql separately and it will work for the null condition but I want this done all together. Below is sample code and data what my example data looks like and what I want.    proc sql; create table WANT as select* from HAVE group by ID having test_scores = max(test_scores) or test_scores is null and Pending = 'YES'; quit; Have ID date test_scores Pending 2A 9/5/19 100 YES 2A 9/5/19   NO 2A 9/5/19   NO 3C 10/21/21   YES 3C 10/21/21   NO 3C 10/21/21   NO   Want  ID date test_scores Pending 2A 9/5/19 100 YES 3C 10/21/21   YES ... View more

Hello ,    How could I select Observations (Rows) where if their Pending column ever says "YES" then output to a new dataset? I am thinking to use proc sql but also, I'm still learning as I go so I feel stuck. Below is my have data and want data tables.    Have ID Test_Date Pending TA 3/29/22 NO TA 3/29/22 NO TA 3/29/22 YES PG 3/29/22 NO PG 4/5/22 NO LT 4/5/22 YES LT 4/5/22 NO LT 4/5/22 NO   Want  ID Test_Date Pending TA 3/29/22 NO TA 3/29/22 NO TA 3/29/22 YES LT 4/5/22 YES LT 4/5/22 NO LT 4/5/22 NO ... View more

Hello,    Is there a method where I can use/call a macro instead of using the same long lines of "IF-THEN" code for data cleaning?    For example I have 4 different datasets and I use the same "IF-THEN" code to clean data from that data set. (they must be separate and not combined). Therefore, I think a macro could be used here instead of writing/copying the same long lines of code. Is this a possibility?    My logic is :   Data Want; set Have; %macro run;   Data Want1; Set Have; If then if then if then; if then if then if then; run Data Want2; set Have; If then if then if then; if then if then if then; run; ... View more

Hello.   I want to use an IF-THEN statement followed by a By statement to create a new variable with a selected date given my IF-THEN statement. It seemed to select my conditions and create a new variable. However, when I do a proc print; the date I selected and my new column returns as a " . " instead of the date it originally had.    How can I fix this? is this a formatting issue? I did do a proc contents and both the old and new date variable are NUM so I'm kind of confused on the issue.    Below is my code and my output.    data compare; set combined; if Pending='YES' then new_date_num = old_date_num; by name; run; name old_date_num date Pending new_date_num FM1 23264 9/11/2023 NO . FM1 23264 9/11/2023 NO . FM1 23264 9/11/2023 NO . FM1 23264 9/11/2023 NO . FM1 23271 9/18/2023 NO . FM1 . 9/11/2023 YES . ... View more

Hello,    I have a small subset of patient data that that includes all their specimen dates. I also have a Pending column where some rows are associated with a "YES" for the Pending column. However I want to create a new column with a "Pending Max Date" where it takes the oldest date in their specimen date and repeats for all their rows so that I can compare the dates for an exclusion.    I was thinking of using Proc SQL and using the max and group by function but I have little experience with proc sql so any help would be great!   Below is what i'd like my table to look like.   Specimendate_all Patient_ID Pending specimen pending max 3/29/22 AB1 NO 4/5/22 3/29/22 AB1 NO 4/5/22 3/29/22 AB1 YES 4/5/22 3/29/22 AB1 NO 4/5/22 4/5/22 AB1 NO 4/5/22 4/5/22 AB1 YES 4/5/22 4/5/22 AB1 YES 4/5/22 4/5/22 AB1 NO 4/5/22 ... View more

Hello,    I have an example subset of 1 patient that where I created 2 separate dates. That is "Date" and "Pending_Date".  I also created a variable where each row is either Pending='YES' or Pending='NO'. Below is my table of what I have and what I want. How can I code if Date is less than Pending_Date then exclude='yes' then I would drop my exclusions.  I tried it using an if then statement but it will only compare dates right next to each other (ex 3/29/2023 > blank date) but i want it to take into consideration the other dates as well. For example Obs 3 is a pending date and not greater Obs 1,2 4,5, and 8 so that wont get excluded. However, Obs 6 is a pending date and is greater than Obs 1,2, and 4 which is how I have my "Want" table.     Again I want my exclusion to be " Pending_Date > Date then Exclude='YES' " but I want all the "Dates" to be taken into consideration.    Whats the best way to approach this?   Thank you in advance!  Have Obs  Patient_ID Pending Pending_Date Date 1 AB1 NO   3/29/22 2 AB1 NO   3/29/22 3 AB1 YES 3/29/22   4 AB1 NO   3/29/22 5 AB1 NO   4/5/22 6 AB1 YES 4/5/22   7 AB1 YES 4/5/22   8 AB1 NO   4/5/22   Want Obs Patient_ID Pending Pending_Date Date 3 AB1 YES 3/29/22   5 AB1 NO   4/5/22 6 AB1 YES 4/5/22   6 AB1 YES 4/5/22   8 AB1 NO   4/5/22 ... View more

Hello  I want to create an exclusion criteria/variable for a large dataset that includes the variables PT_Number, date, status, and rank.    I want to create an exclusion criteria so that I can later use a "Where" statement.  I basically want this exclusion " If the dates where rank does not equal 1 and status does not equal P1 are less than the dates with the rank that equal 1 and status that equal P1 then the Exclusion_Variable will equal 1.     *Note: there are other entries for rank such as (2,3,4) and for status such as (C1 CI, EA, etc). below is what I thought my code should look like and a table that I have and that I want. I also did test my code below and did a freq check and did not get any '1' in my exclusion variable frequency.    Am I missing something?   data Want; set Have; if date and rank ne 1 and status in ('C1,CI,EA,EB,F1,F2,F3,M1,M2,M4,M5,M7,M8,M9,MB,MH,MP,MS,N9,P1,P3,S4,VL,c1') < date and rank='1' and status='P1' then exclusion_variable='1'; run;   Have                                        PT_Number date status rank A1 22733 M1 1 A1 22733 P1 2 A1 22733 P1 1 A1 22733 P1 3 A1 22740 P1 2 A1 22740 P1 1 A1 22740 P1 1 A1 22740 P1 3   Want PT_Number date status rank Exclusion_Variable A1 22733 M1 1 1 A1 22733 P1 2 1 A1 22733 P1 1   A1 22733 P1 3 1 A1 22740 P1 2   A1 22740 P1 1   A1 22740 P1 1   A1 22740 P1 3   ... View more

Hello,    I have multiple observations with the same names with ranks and dates. I basically want to exclude names where if their date is less than their other date with their rank. For example Bob has multiple dates with his name, however, for one date (1/2/23) and he is ranked 1 with a status of P1. There is another row/date for Bob (1/2/22) where he is ranked 2 with a status of M1. The Rank 1 and P1 are my conditions for a top rank.  Therefore, because Bob's date is 1/2/22 in one of the rows and he is rank 2 with a status of M1 then I want to exclude that row. Below are 2 tables of what I have and what I want along with a code. Please let me know whats the best way to approach this as im still learning. Thank you.    Data Want; set Have; *just how im thinking but dont know how to code it; If date is < date where Rank=1 and Status=P1 Run; Have Name Date Rank Status  BOB 1/2/23 1 P1 BOB 1/2/22 2 M1   Want Name Date Rank Status  BOB 1/2/23 1 P1 ... View more