Hi i need help with putting values of Existing cover, Marital_Status, Weight and Height with their colums because they are just mixed up, see below code and results im getting
proc sql;
create table Policy_Previous_Cover as
select distinct
d.id as Policy_id,
d.ExternalReferenceId,
f.text as Answer
from quirc.question as a
inner join quirc.questionprofile as b
on a.questionprofileid = b.id
and b.Externalreferenceid in ('Existing_cover','Marital_status','Weight','Height')
inner join quirc.section as c
on a.sectionid = c.id
inner join quirc.policy as d
on c.questionnaireid = d.questionnaireid
inner join quirc.answer as e
on a.id = e.questionid
inner join quirc.result as f
on e.resultid = f.id
and f.stamp >= DHMS('19MAR2017'd,0,0,0)
group by d.id,d.ExternalReferenceId
having f.stamp = max(f.stamp)
;quit;
Data WANT
id ExternalReferenceID Marital_Status Existing_Cover Weight Height
1 01nfhdkdsdkshd-qqq Single Yes 60 1.65
2 b-e6fdfdfdfoiuiuudsd Married No 80 1.1
3 bffdgdfdfdfdfdfdfdfd Married Yes 55 2.1
... View more
