BookmarkSubscribeRSS Feed
cte1
Calcite | Level 5
I am new to SAS and have run into a problem that I cannot solve. I am trying to count the number of unique codes in a group. Each group consist of multiple rows and the codes are pipe delimited in each cell. A sample of my dataset looks like this:

GROUP CODES
1 |231|322|414|
1 |231|322|2 |231|
2 |231|114|
2
3
3

So in GROUP 1 there are 3 unique codes (231, 322, and 414) and in GROUP 2 there are 2 unique codes (231 and 114) There are 0 codes in GROUP 3. Can anyone tell me how to get SAS to give me an output dataset like this:

Group Count
1 3
2 2
3 0

Thanks in advance.
Chris
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Using a DATA step, read up the record using just an INPUT ; statement (no variables specified), then parse _INFILE_, first to get your GROUP, and then iterate with SCAN through each CODES that is present and do an OUTPUT for each. Then use PROC SORT NODUPKEY with BY on GROUP CODES, and then PROC SUMMARY to get your _FREQ_ count with BY GROUP.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

data step programming introduction site:sas.com Message was edited by: sbb
Peter_C
Rhodochrosite | Level 12
normalise it with proc transpose, so it is just
group, code
then
proc summary (or means or freq)
have a look in the online doc
FloydNevseta
Pyrite | Level 9
Since you are new to SAS, here is one coded solution to your problem.

data sample;
length group 3 code 3;
infile cards dlm='|' truncover;
input
group
code @;
output;
do while ( not missing( code ));
input code @;
if not missing( code ) then output;
end;
cards;
1 |231|322|414|
1 |231|322|2 |231|
2 |231|114|
2
3
3
;
run;

proc sort nodupkey data=sample; * nodup option would also work since all variables are in by-group;
by group code;
run;

* Summary #1: Proc Means;
proc summary data=sample nway;
class group;
var code;
output out=summary (drop=_:) n=count;
run;

* Summary #2: Proc SQL;
proc sql;
create table summary2 as
select
group,
sum( case
when not missing( code ) then 1
else 0
end ) as count
from
sample
group by 1
;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 660 views
  • 0 likes
  • 4 in conversation