Adding values to their columns

[Solly7]

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

[s_lassen · Accepted solution]

You have not supplied any test data, and you SQL is a bit hard to read, but I think you can do it by adding the question type (b.Externalreferenceid) to you output and then transpose:

proc sql;
create table Policy_Previous_Cover as
select distinct
d.id as Policy_id,
d.ExternalReferenceId,
b.Externalreferenceid as question,
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;

proc transpose data=Policy_Previous_Cover out=want;
  by id externalreferenceid;
  var text;
  id question;
run;

Of course, you should sort before transposing, or add an ORDER BY clause to your SQL.