topic Adding values to their columns in SAS Programming

Adding values to their columns

Solly7 — Tue, 25 May 2021 07:02:08 GMT

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

Re: Adding values to their columns

s_lassen — Tue, 25 May 2021 08:37:28 GMT

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.