topic Re: Row calculations in SAS Programming

Row calculations

weronikaz — Sat, 08 Aug 2020 17:18:27 GMT

Hello,

I have the following data set:

Student_id Week1 Week2 Week3 Week4 Week5 Week6

123 0 4 3 0 0 2

124 4 3 4 4 4 4

125 2 0 0 3 2 1

126 0 2 3 0 2 0

127 0 0 0 0 0 0

0 means that the person didn't attend any classes in that week, 1/2/3 means how many classes student attended in the week. I need to produce a report with students who didn't attend any classes for at least 2 weeks in a row. Does anyone have an idea how to do it? Thank you for your help.

Re: Row calculations

Tom — Sat, 08 Aug 2020 17:50:23 GMT

Normally to work with a series of variables you would use an ARRAY statement to allow you use an index.

In this simple case I would just convert the values to a string and use some simple pattern matching.

data have;
  input Student_id $ Week1-Week6;
cards;
123 0 4 3 0 0 2
124 4 3 4 4 4 4
125 2 0 0 3 2 1
126 0 2 3 0 2 0
127 0 0 0 0 0 0
;

data want;
  set have;
  if indexw(catx(' ',of week1-week6),'0 0');
run;

proc print;
run;

       Student_
Obs       id       Week1    Week2    Week3    Week4    Week5    Week6

 1       123         0        4        3        0        0        2
 2       125         2        0        0        3        2        1
 3       127         0        0        0        0        0        0

Re: Row calculations

novinosrin — Sat, 08 Aug 2020 17:54:18 GMT

Hi @weronikaz


data have;
input Student_id     Week1  Week2  Week3  Week4  Week5  Week6;
cards;
123                   0             4           3           0          0            2
124                   4             3           4           4          4            4 
125                   2             0           0           3          2            1
126                   0             2           3           0          2             0
127                   0             0           0           0          0            0
;

data want;
 set have;
 array t week:;
 _n_=countw(cats(of t(*)),'0','k');
 do _n_=1 to _n_;
  if lengthn(scan(cats(of t(*)),_n_,'0','k'))>=2 then do;
   output;
   leave;
  end;
 end;
run;

Student_id	Week1	Week2	Week3	Week4	Week5	Week6
123	0	4	3	0	0	2
125	2	0	0	3	2	1
127	0	0	0	0	0	0

Re: Row calculations

mkeintz — Sat, 08 Aug 2020 18:18:26 GMT

just be sure no one ever attends 10 classes anytime except week 6.

Re: Row calculations

Tom — Sat, 08 Aug 2020 19:48:09 GMT

@mkeintz wrote:
just be sure no one ever attends 10 classes anytime except week 6.

That wouldn't make any difference. Notice the use of INDEXW().

It might catch 0 followed by 0.5 but only if you include period in addition to space in list of delimiters in the INDEXW() call.

Re: Row calculations

novinosrin — Sat, 08 Aug 2020 19:43:34 GMT


   


data have;
input Student_id     Week1  Week2  Week3  Week4  Week5  Week6;
cards;
123                   0             4           3           0          0            2
124                   4             3           4           4          4            4 
125                   2             0           0           3          2            1
126                   0             2           3           0          2             0
127                   0             0           0           0          0            0
;


data want;
 set have;
 if prxmatch('/(0){2,}/', cats(of week1-week6));
run;

Re: Row calculations

Ksharp — Sun, 09 Aug 2020 11:33:48 GMT

data have;
input Student_id     Week1  Week2  Week3  Week4  Week5  Week6;
cards;
123                   0             4           3           0          0            2
124                   4             3           4           4          4            4 
125                   2             0           0           3          2            1
126                   0             2           3           0          2             0
127                   0             0           0           0          0            0
;
data want;
 set have;
 array x{*} week: ;
 do _n_=1 to dim(x)-1;
  if x{_n_}=0 and x{_n_+1}=0 then do;output;leave;end;
 end;
run;