I did a test, works perfect (as usual). Bart ... View more

/* That is not a good behavior for coding. Assuming I understood what you need. */ data HAVE; infile datalines dsd truncover; input ID $ date :mmddyy10. test_scores Pending $; format date mmddyy10.; datalines; 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 ; proc sql; create table WANT as select* from HAVE group by ID having (test_scores = max(test_scores)) and (test_scores is not missing or Pending='YES'); quit; ... View more

Yes, this is likely a good opportunity to try using the macro language.   The main purpose of the macro language is to generate SAS code.  So instead of you having to type SAS code yourself, the macro language can generate SAS code for you.   When I first started writing macros, one of the main triggers for me to consider writing a macro was when I found myself copying a block of code and pasting it multiple times (into one program, or multiple programs).  Placing that code inside a macro will allow you to store the code in one place (the macro definition) and then call the macro anywhere you want to generate that block of code.   As others have said, if you share an example of the data you have, and the IF statements you want to generate, people will be happy to help you develop the macro (and will also be happy to propose non-macro code generation approaches). ... View more

I want to use an IF-THEN statement followed by a By statement  That does not make any sense.  BY statement follow SET/MERGE/UPDATE statements in a DATA step.  Or follow the PROC statement in a PROC step.   Since for the observations where Pending='YES' have a missing value of old_date_num where do you want to get a non-missing value for new_date_num ?   Do you want to remember one of the values of new_date_num from one of the previous observations?   ... View more

@Oligolas wrote: oh yes good point, it's also possible to simply compare to the max since earlier observations must be dropped anyway PROC SQL; CREATE TABLE want AS SELECT * FROM have GROUP BY Patient_ID HAVING pending='YES' or Date>=max(Pending_Date) ORDER BY obs ; QUIT; "Comparing to the max" actually means reading all the data twice, once to get the max, and once to compare. ... View more

It looks like you want the newest date, not the oldest...? Untested: proc sql; create table want as select *, max(case pending when 'YES' then specimentdata_all else 0 end) as specimen_pending_max from have group by patient_id; quit;   ... View more

Double DO loop: data want; _date = 99999; do until (last.pt_number); set have; by pt_number; if status ne "P1" or rank ne 1 then _date = min(_date,date); end; do until (last.pt_number); set have; by pt_number; if status = "P1" and rank = 1 and date gt _date then exclusion_variable = 1; output; end; drop _date; run; This applies your rule, but it won't create your expected output, as 22733 is not greater than any other date. ... View more

Double DO loop: data want; _date = 99999; do until (last.pt_number); set have; by pt_number; if status ne "P1" or rank ne 1 then _date = min(_date,date); end; do until (last.pt_number); set have; by pt_number; if status = "P1" and rank = 1 and date gt _date then exclusion_variable = 1; output; end; drop _date; run; This applies your rule, but it won't create your expected output, as 22733 is not greater than any other date. ... View more

If your selection rule is simply picking the top rank then try this: Data Want; set Have; where Rank='1' and Status='P1'; run; ... View more

@rebelde52 wrote: This is helpful! Question though. I checked my data freq by the unique_id on a specific id #. For example dateset1 had 8 observations for that id#. Dataset2 had 16 for that specific id#. Joineddataset had 128 observations for that specific id#. Therefor it seemed like it multiplied? Why is that? Do I just deduplicate it? That means you've got a many:many relationship between your tables and unique_id is not unique.  Even a SQL inner join will return 8X16 rows which is what you observe.   How would you deduplicate? You would need for at least one of the tables all variable values of interest to be identical for a specific "unique" id. Is that the case? If not then which values would you pick in order to "dedup"?     ... View more

If there actually is a comma in a directory name, get rid of it the day before yesterday. Blanks are bad enough, but a comma would simply be insane. ... View more

@rebelde52 wrote: Could I still add the %bquote if my macro variable is blank? for example doc_path= ; ?? Not needed.   But it shouldn't matter as long as you don't accidentally include a space inside the %BQUOTE() function call. ... View more

@rebelde52 wrote: Could I still add the %bquote if my macro variable is blank? for example doc_path= ; ?? Not needed.   But it shouldn't matter as long as you don't accidentally include a space inside the %BQUOTE() function call. ... View more

@rebelde52 wrote: Could I still add the %bquote if my macro variable is blank? for example doc_path= ; ?? Not needed.   But it shouldn't matter as long as you don't accidentally include a space inside the %BQUOTE() function call. ... View more

@whymath wrote: I guess you log may give some hints, could you take a screen shot on you log window? Actually better is to copy the TEXT from the log window, on the forum open a text box using the </> icon above the message window and paste the text. The text box retains formatting that the message window will remove. Text is better as it allows us to copy relevant pieces of the report and point out what the messages may mean and/or modify the code. INCLUDE the all the code and messages related to the procedure call.   ... View more