Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- What is an elegant way to count these flags (including zeros for rows ...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-25-2023 11:39 AM
(360 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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

"

Hands-on-Workshop: "

"

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

SAS Documentation

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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

"

Hands-on-Workshop: "

"

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

SAS Documentation

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Very nice and elegant solution. Thanks, Yabwon!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

😉

_______________

**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

"

Hands-on-Workshop: "

"

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

SAS Documentation

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.