topic Re: How to create a Variable that have the list of variables with missing values. in SAS Programming

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

SASuserlot — Tue, 06 Sep 2022 18:33:39 GMT

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

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

PeterClemmensen — Tue, 06 Sep 2022 18:38:24 GMT

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;

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

PeterClemmensen — Tue, 06 Sep 2022 18:40:19 GMT

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

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

SASuserlot — Tue, 06 Sep 2022 18:53:14 GMT

It worked. Thank you.

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

PeterClemmensen — Tue, 06 Sep 2022 18:55:18 GMT

Anytime 🙂

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

PaigeMiller — Tue, 06 Sep 2022 18:56:53 GMT

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?

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

SASuserlot — Tue, 06 Sep 2022 19:06:19 GMT

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.