Filtering values prior to query

AshJuri — Wed, 08 Apr 2020 17:40:49 GMT

Hi, I want to calculate the current value divided by the minimum value in column "measurement" from visits prior to the current visit in column "type". However, first I need to use the column "jsw" to filter for visits prior to the current visit value, and then calculate the minimum from those prior visits. This would require first searching for the minimum" jsw" value, then looking for records specific to the where "jsw" is less than the minimum "jsw" value found and then from that subset of records, pull the minimum "measurement" value for the division calculation. However, I cannot figure out to implement this in my current working code. Any solutions to this matter would be appreciated.

data have;
	input Subject Type :$12. Date &:anydtdte. jsw procedure :$12. measurement;
	format date yymmdd10.;
	datalines;

Subject Type      Date                jsw                procedure     measurement      
500   Initial    15 AUG 2017            6                   Invasive        20        
500   Initial    15 AUG 2017            9                   Surface         35        
500   Followup   15 AUG 2018            8                   Invasive        54       
428   Followup    15 AUG 2018          56                   Outer           29        
765   Seventh     3 AUG 2018           12                   Other           13     
500   Followup    3 JUL 2018           23                   surface         98    
428   Initial     3 JUL 2017           34                   Outer           10    
765   Initial     20 JUL 2019          4                    Other           19     
610   Third       20 AUG 2019         58                    Invasive        66   
610   Initial     17 Mar 2018         25                    Invasive        17    

PROC SQL;
    create table want as
    select a.*,
        ((select measurement as lastmeasurement
         from have
         where Subject = a.Subject and procedure= a.procedure 
         having jsw = max(jsw)) -min(a.measurement))/ min(a.measurement)*100 as WantedPercent
    from have as a
    group by Subject, type
    order by Subject, type, date;
QUIT;

Re: Filtering values prior to query

RicHen — Thu, 09 Apr 2020 16:59:27 GMT

I'm having difficulties understanding exactly what you want.

Lets do it step by step.

First you need to find the current visit.

proc sql;
create table current_visit AS 
select
     t1.Subject, 
     max(t1.Date) format=YYMMDD10. as Date
from have t1
group by t1.Subject;
quit;

Now find the previous visits:

proc sql;
create table previous_visits as 
select
     t1.Subject, 
     t1.Type, 
     t1.Date, 
     t1.jsw, 
     t1.procedure, 
     t1.measurement
from have t1
left join current_visit t2 on (t1.Subject = t2.Subject)
where t1.Date < t2.Date;
quit;

And now I'm lost where to go. Not even sure if the path was right.

But manly do it step by step.

One table for each value.

topic Filtering values prior to query in SAS Data Management

Filtering values prior to query

Re: Filtering values prior to query