Help with pulling out last data from a time period and first data from the next

[EdwardsL8]

Hello, 

I am able to pull the data that I need out of the last of the first time frame and then out of the first of the second selected time by using the proc sql statement as such: 

proc sql;
create table x2018 as
select *
from work.completion_status
where Titer ne 'x' and COLLECT2018='YES'
group by Patient_ID
having Lab_1_Spec_Collection_Date= min(Lab_1_Spec_Collection_Date);
quit;

proc sql;
create table xnot2018 as
select *
from work.completion_status
where Titer ne 'x' and COLLECT2018='YES'
group by Patient_ID
having Lab_1_Spec_Collection_Date= max(Lab_1_Spec_Collection_Date);
quit;

However, I cannot figure out how to compare these data. 

Each of these data that are being pulled are attached to a patient ID and I want to compare an already recoded value in the first dataset to the second dataset.

For instance PTX has their last lab value prior to 2018 in 2015 and the attached value is 1. PTX also has a 2018 lab value and the first one is 4. I want SAS to find these values and essentially calculate 1-4=(-3).

I want to be able to compare this among all PatientIDs. 

Thank you!

[PGStats]

I think you could use correlated subqueries to get this:

create table want as
select unique Patient_ID,
(   select mean(PTX)
    from completion_status
    where Patient_ID = a.Patient_ID and Titer ne 'x' and COLLECT2018='YES'
    having Lab_1_Spec_Collection_Date= min(Lab_1_Spec_Collection_Date)) - 
(   select mean(PTX)
    from completion_status
    where Patient_ID = a.Patient_ID and Titer ne 'x' and COLLECT2018='YES'
    having Lab_1_Spec_Collection_Date= min(Lab_1_Spec_Collection_Date)) as PTXdiff
from completion_status as a;

(untested)

[EdwardsL8]

So I have gotten closer, 

but now I need to take these patient_IDs and find the difference in the dilution between these selected lab values. 

What is the correct way to write the below code?

data work.import5;
set work.import4;
by Patient_ID;
*result= ((DILUTION where x2018='YES')-(DILUTION where xPRE2018='YES'));
run; 

Thank you! 

[PGStats]

Once you understand the code above, extending it to meet your new requirement which has the same structure as the previous, will seem trivial.