I have a data set that I need to reorganize into columns and I'm worried there will be too much risk for human error if I try to hard code them all. Data set example:
ID
QUESTN_CD
QUESTN_TXT
QUESTN_ANSWER_TXT
123
1
Gender
M
123
2
Age
36
123
3
Fulltime
Y
123
4
Hourly Pay
N
My desired output is something like this:
ID
GENDER
AGE
FULLTIME
HOURLY PAY
123
M
36
Y
N
Code example with dummy data:
PROC SQL;
CREATE TABLE QUESTNS AS
SELECT DISTINCT t1.*,
t2.QUESTN_ANSWR_TXT AS 'Contract Signed'n,
t3.QUESTN_ANSWR_TXT AS 'Assignment Start Date'n,
t4.QUESTN_ANSWR_TXT AS 'Correct W4'n,
t5.QUESTN_ANSWR_TXT AS 'Name Spelled Properly'n,
t6.QUESTN_ANSWR_TXT AS 'Alterations to Assignment'n,
t7.QUESTN_ANSWR_TXT AS 'Assignment Type'n
FROM WORK. PERSON_INFO t1
left join QUESTN t2 on t1.ID = t2.REF_ID
left join QUESTN t3 on t1.ID = t3.REF_ID
left join QUESTN t4 on t1.ID = t4.REF_ID
left join QUESTN t5 on t1.ID = t5.REF_ID
left join QUESTN t6 on t1.ID = t6.REF_ID
left join QUESTN t7 on t1.ID = t7.REF_ID
WHERE (t2.QUESTN_CD = 9 AND t3.QUESTN_CD = 23 AND t4.QUESTN_CD = 8 AND t5.QUESTN_CD = 29
AND t6.QUESTN_CD = 37 AND t7.QUESTN_CD = 22);
QUIT;
But I think there's too much room for human error in manually renaming each column based on the question code, given that there are over 40 question codes (for now) and the data source may update and change without my knowledge. Any and all advice appreciated!
... View more
