topic Re: How to sum up the valid responses from some variables ignore the missing values in SAS Data Management

How to sum up the valid responses from some variables ignore the missing values

CynthiaWei — Tue, 10 Mar 2020 18:46:46 GMT

Hi SAS Pros,

I am have a data like this:

Have:

Var1	Var2	Var3	Var4	Var5	Var6
1	2	0	.	.	.
0	.	1	1	1	1
2	0	.	.	2	2
1	2	1	1	2	4

I want to sum up the valid responses from these variables and ignore the missing values.

Want:

Var1	Var2	Var3	Var4	Var5	Var6	Total
1	2	0	.	.	.	3
0	.	1	1	1	1	4
2	0	.	.	2	2	6
1	2	1	1	2	4	11

Thank you in advance for any help!

Best regards,

Re: How to sum up the valid responses from some variables ignore the missing values

novinosrin — Tue, 10 Mar 2020 18:57:50 GMT

data have;
input Var1	Var2	Var3	Var4	Var5	Var6;
cards;
1	2	0	.	.	.
0	.	1	1	1	1
2	0	.	.	2	2
1	2	1	1	2	4
;

data want;
 set have;
 total=sum(of var1-var6);
run;
/*Or explicitly define in an array*/
data want;
 set have;
 array v var1 var2 var3 var4 var5 var6;
 total=sum(of v(*));
run;
/*Or with a double dash if the number suffix is not consecutive
but the arrangement is consecutive*/
data want;
 set have;
 array v var1--var6;
 total=sum(of v(*));
run;
/*Or a double dash without array grouping if the /
arrangement of variables is consecutive*/
data want;
 set have;
 total=sum(of  var1--var6);
run;

Re: How to sum up the valid responses from some variables ignore the missing values

CynthiaWei — Tue, 10 Mar 2020 20:50:43 GMT

Thank you so much for you prompt reply!

What if all the missing in the Have data set is replaced by some negative values, i.e. -1, -3, -4. The negative values represent different reasons for missing. It just those negative values are the initial responses available in my data set, and I want to treat them as missing. Can I still use the sum (of ) code?

Best regards,

Re: How to sum up the valid responses from some variables ignore the missing values

novinosrin — Tue, 10 Mar 2020 20:55:25 GMT

Hi @CynthiaWei You could reset the negative values to missing and then sum like->

data want;
 set have;
 array v var1--var6; 
 do over v;
  if v<0 then v=.;
 end;
 total=sum(of  var1--var6);
run;

Re: How to sum up the valid responses from some variables ignore the missing values

CynthiaWei — Tue, 10 Mar 2020 21:11:05 GMT

Thank you for the code. But in my actual data set the variables are not array, the names are like orange, apple, pear, and kiwi. What the code should be like if this is the case?

Regards,

Re: How to sum up the valid responses from some variables ignore the missing values

novinosrin — Tue, 10 Mar 2020 21:12:53 GMT

You could explcitly group the names into an array.

For example:

/*Fruits is the name of the array*/

array fruits  orange apple pear  kiwi;

Re: How to sum up the valid responses from some variables ignore the missing values

ballardw — Tue, 10 Mar 2020 21:55:48 GMT

@CynthiaWei wrote:

Thank you so much for you prompt reply!

What if all the missing in the Have data set is replaced by some negative values, i.e. -1, -3, -4. The negative values represent different reasons for missing. It just those negative values are the initial responses available in my data set, and I want to treat them as missing. Can I still use the sum (of ) code?

Best regards,

Unless you have more than 27 codes you should replace the -1, -3, -4 with SAS special missing values. These are .A to .Z and ._ (note the preceding dot just like normal missing).

You can assign a format to display the meaning of the special missing but for calculations and modeling the missing will be excluded.

You can even read the into special missing if reading the data with a data step.

proc format library=work;
invalue mymiss 
'-1' = .A
'-2' = .B
'-3' = .C
other = [best8.]
;
value mymiss
.A = 'Not Recorded'
.B = 'Refused to Answer'
.C = 'Score out of Range'
;
run;

data example;
   informat var1 - var3 mymiss. ;
   input var1 - var3;
   total = sum(of var:);
datalines;
1 2 3
4 -1 6
5 4 -2
-3 -2 -1
;

proc print data=example;
   format var1 - var3 mymiss.;
run;

With existing data you would use a data step and code like:

if var1= -1 then var1= .A;

Note the value can be .a or .A but will appear in table views as the upper case letter as they will in Proc Print output without a format.

If you run the data through proc freq without the MISSING option on the tables statement then the missing values do not appear. If you use MISSING without a format then the special missing character appears. If you use a format with missing then you get the formatted value.