CODEWITHSUNDEEP
sqlsas
Plutos
Plutos

Reputation: 51

how to query multi where conditions in sas proc sql

I want to show result of proc query of 'where conditions' using sas sql.

what is patient_name that have disease_code of 'I48' taking the medicine like ''243tab" and "246tab" from 2010 to 2021 for the first time? -nm_cd="243tab" and "246tab"

-disease_code=I48

-year=between 2010 and 2021

-count = 1(taking the medicine for the first time)

Using this example conditions, I already conducted query as follows.

 '''query code'''
 
 option compress=yes;
 libname p "/user/temp/data_source";

 proc sql;
 create table m_join as
 select index_num, R_key, patient_name from p.m1 as t1 join p.m2 as t2 
 on t1.index_num=t2.index_num join p.m3 as t3 join p.m4 as t4
 on t3.index_num=t4.index_num
 where stubstr(t4.nm_cd) in ("243tab" and "246tab")
 and count = "1"
 and t1.year=between "2010" and "2021"
 and disease_code = "I48"
 group by index_num;
 quit;

But this code inculdes some problems.

I don't know how to solve this problem.

Please, let me know how to solve this problem.

Upvotes: 0

Views: 978

Answers (1)

Dmitry Pukhov
Dmitry Pukhov

Reputation: 168

  1. Explain tables contents' so it is easier to give full SQL query.
  2. Format query so it is easier to read.
  3. If you need to find patients then group by patient.
  4. Condition on taking medicine is unclear. I will assume that we need to find a person who took the medicine like 243tab and 246tab eventually but the first time they took it was between 2010 and 2021.
  5. Use single quotes for literals.
  6. Write all join conditions.
  7. Do not use strings when compare years because years are numbers (I hope you store them as numbers).
  8. In clause requires to use commas to list values, not 'ands'.
  9. Use aliases every time. Again the code will be easier to read.
proc sql;
    create table m_join as
    select patient_name
    from p.m1 as t1
    join p.m2 as t2 
    on t1.index_num = t2.index_num
    join p.m3 as t3
    on ???
    join p.m4 as t4
    on t3.index_num=t4.index_num
    where disease_code = 'I48'
        and t4.nm_cd in ('243tab', '246tab')
    group by patient_name
    having min(t4.year) between 2010 and 2021
    ;
quit;

Upvotes: 1

Related Questions