How to create a Variable that have the list of variables with missing values.

[SASuserlot]

I am looking to create an additional variable that show the list of variables that have the missing values. Is it possible?

data have;
  input id  x1 y1 z1 ;
  cards;
 106 1 . 2
 107 3 . .
 108 . . .
 109 1 2 3
 ;

 run;

Have:

Want

In the 'Have' dataset  For example: for 106 subject missing values for x1, y1, z1. then I am expecting to create a variable 'missvar'  with variable list separated by delimiter of any kind. is this possible to achieve? Thanks

[PeterClemmensen · Accepted solution]

Try this

data have;
  input id  x1 y1 z1 ;
  cards;
 106 1 . 2
 107 3 . .
 108 . . .
 109 1 2 3
 ;

data want;
   set have;
   array a x1 -- z1;
   length missvar $200;
   
   do over a;
      if a = . then missvar = catx(', ', missvar, vname(a));
   end;
run;

[SASuserlot]

It worked. Thank you.

[PeterClemmensen]

Anytime 🙂

[PaigeMiller]

I'm curious, @SASuserlot , how does having such a text string such as 'x1,y1,z1' help anything? What will you do with it once you create it?

[SASuserlot]

This is an example simulate my data to check data errors. We have have   two questions which gate the next set of Numeric  Score for next set of question.

Q1, Q2 are Yes or No questions. Q3,Q4, Q5---Q100 are numeric scores questions. Q3, Q4, Q5---Q100 and Q Total, only given score when  at least Q1 or Q2 Need to be 'Yes'. 'Q total'  only Calculated when all the questions are answered.

So we have the followinng situations where

Q1='Y' and Q2='Y' but some 'Qx'  missing the scores and Q total was created.   We need to output the list of all those questions missing the  scores as list  showing why it's a data error with flags in xlsx file.

I hope it makes some kind of sense.

[PeterClemmensen]

@SASuserlot beware that there is a difference between your have data in the image and your posted data step.