What is an elegant way to count these flags (including zeros for rows that don't have a "Y")?

[Hello_there]

Hi, the purpose of this exercise is to create a table showing how many subjects populate for each flag. This is a data manipulation exercise, and the main thing i'm struggling with is trying to output a 0 count for flag2. Ideally, I'd like to be able to do it in PROC SQL.

data have; 
infile datalines dsd dlm=",";
	input subjid $ trtan flag1 $ flag2 $ flag3 $ flag4 $ flag5 $;
datalines;
001, 1, Y, , , Y, Y
002, 2, , , , ,
003, 1, Y, , Y, Y, 
004, 2, Y, , Y, ,
005, 2, , , , Y,
006, 2, , , , , Y
007, 1, Y, , Y, , 
008, 1, Y, , , , Y
009, 2, Y, , , , 
010, 1, , , y, , 
;
run;


data have1; set have;
output;
trtan=3; output;
run;

The output is supposed to have 4 columns, and 5 rows. The first two rows are only supposed to output values for the flag label column and overall column (TRTAN3).

Desired output;

FLAG   TRTAN1 TRTAN2 TRTAN3

Flag1                                      6

Flag2                                      0

Flag3        3                 1         4

Flag4        2                 1         3

Flag5        2                 1         3

[Reeza]

SQL will need to be hardcoded for this type of analysis. Use a different approach here.

[Reeza]

Does that desired output align with the data? I don't see any TRTAN3 in the data?

[Hello_there]

Hi Reeza, it's in the second data step where i output it.

[yabwon · Accepted solution]

data have; 
infile datalines dsd dlm=",";
	input subjid $ trtan flag1 $ flag2 $ flag3 $ flag4 $ flag5 $;
datalines;
001, 1, Y, , , Y, Y
002, 2, , , , ,
003, 1, Y, , Y, Y, 
004, 2, Y, , Y, ,
005, 2, , , , Y,
006, 2, , , , , Y
007, 1, Y, , Y, , 
008, 1, Y, , , , Y
009, 2, Y, , , , 
010, 1, , , y, , 
;
run;
proc print;
run;

data have1; set have;
output;
trtan=3; output;
run;
proc print;
run;

data have2;
  set have1;
  N=_N_;
run;

proc transpose data=have2 out=have2t;
  by n trtan;
  var flag:;
run;
proc print;
run;

proc sql;
  create table have3 as
  select trtan, _NAME_ as flag, count(col1) as x
  from have2t
  group by _NAME_, trtan
  ;
quit;

proc transpose data=have3 out=want(drop=_NAME_) prefix=trtan;
  by flag;
  var x;
  id trtan;
run;
proc print;
run;

[Hello_there]

Very nice and elegant solution. Thanks, Yabwon!

[yabwon]

😉

[ballardw]

---

@Hello_there wrote:

Hi, the purpose of this exercise is to create a table showing how many subjects populate for each flag. This is a data manipulation exercise,

---

Is this exercise in anyway supposed to be extendable to more observations or variables?

Please note that you have one value of lowercase y in your data. So are we supposed to count just Y or include the y as well?

If this "exercise" is homework what have you actually tried?

What if you create variables that have values of 1 instead of Y and 0 instead of missing (VERY easily done) then sums get counts and 0. But you are reshaping data twice with the totals so do not expect anything "elegant", whatever that means in this  case.

I can do such, here is one example but I would not want to extend this for many more "exceptions" to your which flags gets summarized by which Trtan values.

data have; 
infile datalines dsd dlm=",";
	input subjid $ trtan flag1 $ flag2 $ flag3 $ flag4 $ flag5 $;
datalines;
001, 1, Y, , , Y, Y
002, 2, , , , ,
003, 1, Y, , Y, Y, 
004, 2, Y, , Y, ,
005, 2, , , , Y,
006, 2, , , , , Y
007, 1, Y, , Y, , 
008, 1, Y, , , , Y
009, 2, Y, , , , 
010, 1, , , Y, , 
;
run;

data temp;
  set have;
  array f(*) flag: ;
  length row $ 6;
  do i= 1 to dim(f);
     row=vname(f[i]);
     value= (f[i]='Y');
     output;
  end;
  keep row trtan value;
run;

proc summary data=temp;
   class row trtan;
   var value;
   output out=summary sum=;
run;

data want (rename=(row=flag));
   set summary;
   array t (*) trtan1 trtan2;
   retain trtan1 trtan2 trtan3;
   if (row in ('flag1' 'flag2') and _type_=2 )
     or (row in ('flag3' 'flag4' 'flag5') and _type_=3);
   by row notsorted;
   if first.row then call missing(trtan1,trtan2,trtan3);
   if row in ('flag1' 'flag2') then trtan3=value;
   else do;
      t[trtan]=value;
      trtan3=sum(trtan3,value);
   end;
   if last.row;
   keep row trtan1-trtan3;

run;

I wouldn't even contemplate SQL for such personally. One main reason: there is some point where you have to do the same things for multiple variables which typically points to an array and SQL does not support arrays. So leads to extremely verbose code and/or multiple passes through the data.