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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: