topic Re: Loop through several similar columns in dataset, output only if populated, and stack data vertic in SAS Programming

Loop through several similar columns in dataset, output only if populated, and stack data vertically

hawkmoon — Thu, 05 Jul 2018 20:49:15 GMT

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.

Re: Loop through several similar columns in dataset, output only if populated, and stack data vertic

Astounding — Thu, 05 Jul 2018 23:03:03 GMT

Mildly quick and easy would be to use arrays:

data want;

set have;

array comm {3} comment1-comment3;

do k=1 to 3;

if comm{k} > ' ' then do;

comment = vname(comm{k});

FieldValue = comm{k};

output;

end;

keep comment PID FieldValue;

run;

Re: Loop through several similar columns in dataset, output only if populated, and stack data vertic

Tom — Thu, 05 Jul 2018 23:20:34 GMT

Use proc transpose. Add a WHERE= condition on the output dataset.

proc transpose data=have 
  out=want (rename=(col1=comment) where=(not missing(comment)))
;
  by pid ;
  var comment1-comment3 ;
run;

Re: Loop through several similar columns in dataset, output only if populated, and stack data vertic

hawkmoon — Thu, 05 Jul 2018 23:31:49 GMT

Thank you both for your very quick and much more palatable solutions!

Quick follow-up question: Do my variables have to have numeric suffixes for these solutions to work? I.e., I used varnames "Comment1, Comment2, etc." as an example, but really they have names like "medication_spec", "ethnicity_spec", etc.

Thank you again! I can already see multiple used for both these sets of code in my work.

Re: Loop through several similar columns in dataset, output only if populated, and stack data vertic

Tom — Fri, 06 Jul 2018 00:44:00 GMT

No.

You can use any list of variables in an ARRAY or VAR statement.