Hello, I have a dataset with multiple “comments” fields which largely remain empty. I want to create a QA dataset which basically dumps each of these fields when populated, and reformats the data vertically. Dataset looks like this: PID, comment1, comment2, comment3 111,, ‘Not completed on this date.’ 112,,, 113, ‘Comment comment’, ‘LTFU’, 114, ‘LTFU,, ‘No result as of 1/1/2018.’ I want to create a dataset which looks like this: Comment,PID,FieldValue Comment1, 113, ‘Comment comment’ Comment1, 114, ‘LTFU’ Comment2, 113, ‘LTFU’ Comment3, 111, ‘Not completed on this date.’ Comment3, 114, ‘No result as of 1/1/2018.’ As more of an sql-programmer, my back-up way to do this would be: proc sql feedback; create table want as select ‘comment1’ as Comment, PID, Comment1 as fieldvalue from work.have where comment1<>’’ UNION ALL select ‘comment2’ as Comment, PID, Comment2 as fieldvalue from work.have where comment2<>’’ …etc… But there must be a more efficient (and no doubt blindingly basic) way to do this... Thank you for your input/patience.
... View more