BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Solly7
Pyrite | Level 9

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;

 

 

Solly7_0-1621925813723.png

 

                                              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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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.

 

 

View solution in original post

1 REPLY 1
s_lassen
Meteorite | Level 14

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.

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 540 views
  • 0 likes
  • 2 in conversation