topic Re: sum up the number of observations from different columns in SAS Programming

sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 03:40:37 GMT

Hello,

I have a dataset with columns as below:

AA_005 AA_010 AA_015 AA_020 AA_025 AA_030 AA_035

1 1 2 3 5 2 1

2 2 5 1 3 3 2

5 4 1 1 2 3 1

1= excellent 2= very good 3= good 4=same 5= bad

I want to create a variable of sum of observations responded 1 or 2 or 3 or 4 but not 5 for any variables (AA_005 to AA_035).

I know how to do it with the if ... then statement. But I'm looking for an easier/shorter way.

Thank you.

Re: sum up the number of observations from different columns

Reeza — Wed, 23 Dec 2020 04:16:45 GMT

Arrays? But that includes IF/THEN as well....it helps if you show what you've done, so not sure if that's an improvement over your current approach or not.

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

Re: sum up the number of observations from different columns

Shmuel — Wed, 23 Dec 2020 08:14:23 GMT

Is this what you are looking for:

data have;
  input AA_005  AA_010  AA_015  AA_020  AA_025  AA_030  AA_035;
datalines;
  1  1  2  3  5  2  1
  2  2  5  1  3  3  2
  5  4  1  1  2  3  1
; run;

data want;
 set have;
     retain sum1-sum5 0;
     array si {7} AA_:;
     array sm {5} sum1-sum5;
     do i=1 to dim(sm); sm(i)=0; end;
     do i=1 to dim(si);
        sm(si(i)) = sm(si(i)) +1;
     end;
     keep sum1-sum5;
     label 
	     sum1=excellent      
	     sum2=very good      
	     sum3=good      
	     sum4=same   
	     sum5=bad
      ;
run;
proc print data=want label; run;

result list:

Re: sum up the number of observations from different columns

andreas_lds — Wed, 23 Dec 2020 10:59:28 GMT

Because hardly anybody here is working for the NSA you have to post the code that you want to be optimized. And it is also not clear, what you expect as result, so you may want to explain what you are looking for.

And, as always, i would start by fixing the data-format you are using: long beats wide in almost all cases i have seen so far.

data have;

   input AA_005 AA_010 AA_015 AA_020 AA_025 AA_030 AA_035;
   datalines;
1 1 2 3 5 2 1
2 2 5 1 3 3 2
5 4 1 1 2 3 1 
;


data haveWithId;
   set have;
   
   id = _n_;
run;

proc transpose data=haveWithId out=transposed name=AA;
   by id;
   var AA:;
run;

proc format;
   value Excellent2Bad (notsorted)
      1 = 'excellent'
      2 = 'very good'
      3 = 'good'
      4 = 'same'
      5 = 'bad'
   ;
run;

proc report data=transposed;
   columns AA Col1;
   
   where Col1 < 5;
   
   define AA / group 'AA';
   define Col1 / across 'Rating' format= Excellent2Bad. order=data;
run;

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 11:33:11 GMT

Thank you for taking the time to respond.

As for if/then statement, I mean to write an if/then statement for each condition, for example:

sum=0

if AA_005 ^= 5 or

AA_010 ^= 5 or

AA_015 ^= 5 or

AA_035 ^= 5 then

sum=sum +1

...

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 11:34:14 GMT

Thank you!

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 11:46:12 GMT

I also made this code with Array, and I hope it's correct.

data new;

set mydata;

array myarray AA_:;

sum = 0;

do i=1 to dim(myarray);

if myarray[i] not in 5 then do;

sum = sum + 1

end;

I'd like to know if there is an easier way to define AA_ % without going through all array statements. Thanks!

Re: sum up the number of observations from different columns

Shmuel — Wed, 23 Dec 2020 12:22:17 GMT

Your code seems to be correct, depending on expected result.

Do you sum per observation or for the accumulated for the whole dataset?

If you sum per dataset then you need the RETAIN statement otherwise SUM will be reset on every observation.

Pay attention that if you need just one statement in a condition you don't need the DO;...END; block.

Next code is equivalent to your code:

data new;
    retain sum.
    set mydata;
    array myarray AA_:;
    sum = 0;
    do i=1 to dim(myarray);
       if myarray[i] not in 5 then sum = sum + 1;
    end;
run;

Re: sum up the number of observations from different columns

Shmuel — Wed, 23 Dec 2020 12:24:21 GMT

Please post your expected result:
- is it a dataset or a report?
- if a report - in what format?

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 12:30:28 GMT

Thank you for your time. Would you please explain a little bit more about the retain? I had "retain" in my code but didn't make any difference. what I want is to accumulate the # of total observations from columns AA_ %, if the response is not 5.

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 12:43:42 GMT

It is a dataset.

Re: sum up the number of observations from different columns

Shmuel — Wed, 23 Dec 2020 12:53:41 GMT

Suppose each observation represent one student.

without RETAIN you'll get non 5 per student (in the sample that is 4, 6, 4 ) while

with RETAIN the result will be: 4, 10 (=6+4), 14 (=4+10).

without RETAIN the SUM variables is assigned to missing each time a new observation is read,

RETAIN means to keep the value and not been reset on reading new observation but it can be

changed (including reset) programmatically in the code.

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 12:55:20 GMT

Thank you!

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 14:49:05 GMT

Hi Samuel,

I don't want to sum up the value for AA_% variables. I want to sum up the number of observations. For example, I have 6000 observations responded to AA_% variables as 1=excellent, .......... 5=bad. I would like to calculate the total observations answered 1 or 2 or 3 or 4 to each of AA_% variable. Thanks.

Re: sum up the number of observations from different columns

andreas_lds — Wed, 23 Dec 2020 14:57:10 GMT

@mandan414 wrote:

Hi Samuel,

I don't want to sum up the value for AA_% variables. I want to sum up the number of observations. For example, I have 6000 observations responded to AA_% variables as 1=excellent, .......... 5=bad. I would like to calculate the total observations answered 1 or 2 or 3 or 4 to each of AA_% variable. Thanks.

Please show the expected output using the data you have posted.

The transposed version of the data i have posted could be used as input for proc freq, like:

proc freq data=transposed;
   where Col1 < 5;
   table col1 / nocum nopercent;
   format col1 Excellent2Bad.;
run;

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 15:24:08 GMT

Let's say our total observations is 6000, if 500 participants answered "5" as the response, the expected frequency output for "sum" should be for example this:

frequency

1 1000

2 2000

3 1000

4 1500

thank you!

Re: sum up the number of observations from different columns

Shmuel — Wed, 23 Dec 2020 15:33:07 GMT

@mandan414 wrote:

Hi Samuel,

I don't want to sum up the value for AA_% variables. I want to sum up the number of observations. For example, I have 6000 observations responded to AA_% variables as 1=excellent, .......... 5=bad. I would like to calculate the total observations answered 1 or 2 or 3 or 4 to each of AA_% variable. Thanks.

Option-1. Next is the result of one of my previous post, including the code:

you can sum totals (last row) of any score(s) you want.

Options-2. Use @andreas_lds code to transpose and run proc freq or compute any score value using RETAIN within a data step.

Option-3 is to make to adapt my code, to the requirement:

data want;
 set have end=done;
     retain sum 0;
     array si {7} AA_:;
     do i=1 to dim(si);
        if sm(si(i)) ^= 5 then sum+1; /* equal to sum=sum+1 */
     end;
     keep sum;
	 label sum='Total number of scoes < 5 ';
run;
proc print data=want label; run;

There are more options but I prefer not to confuse you with it.

Re: sum up the number of observations from different columns

Shmuel — Wed, 23 Dec 2020 16:09:01 GMT

@mandan414 wrote:

Let's say our total observations is 6000, if 500 participants answered "5" as the response, the expected frequency output for "sum" should be for example this:

          frequency

1         1000

    2            2000

3           1000

    4            1500

thank you!

Within the small sample:

data have;

   input AA_005 AA_010 AA_015 AA_020 AA_025 AA_030 AA_035;
   datalines;
1 1 2 3 5 2 1
2 2 5 1 3 3 2
5 4 1 1 2 3 1 
;
run;

do you expect to sum=1, as only one observation does not contain a score of 5 ?

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 16:59:37 GMT

Yes.

Re: sum up the number of observations from different columns

mandan414 — Wed, 23 Dec 2020 17:00:42 GMT

Thank you so much. How about my array codes? I think that works too, am I right?