BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Lapis Lazuli | Level 10

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Meteorite | Level 14

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&&

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;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

7 REPLIES 7
Super User

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&

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

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&

Does that desired output align with the data? I don't see any TRTAN3 in the data?
Lapis Lazuli | Level 10

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&

Hi Reeza, it's in the second data step where i output it.
Meteorite | Level 14

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&&

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;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Lapis Lazuli | Level 10

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&

Very nice and elegant solution. Thanks, Yabwon!
Meteorite | Level 14

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&&

😉

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Super User

## Re: What is an elegant way to count these flags (including zeros for rows that don't have a "Y&&

@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.

Discussion stats
• 7 replies
• 361 views
• 9 likes
• 4 in conversation