topic Re: Count Unique Values by Row and Use Values as Column Names in SAS Programming

Count Unique Values by Row and Use Values as Column Names

cobba — Tue, 07 Dec 2021 00:14:01 GMT

Using SAS EG v7.1

I have the following data where an event is represented by an ID and any number of codes (always strings) can be applied to each event.

data have;
    input id$ code$ @@;
    datalines;
    aa x1 aa x1 aa x2
    bb x1 bb y1 bb y1 bb z2 bb z4
    cc x1 cc x2 cc y1 cc x1 cc x1 cc q1
    dd z2 dd z2 dd z2 dd q1
    ;
run;

Episode IDs and Codes are always strings but can be of varying lengths.

The data below is what I want. Where the unique codes make up the column headings and the count is performed for each ID.

Re: Count Unique Values by Row and Use Values as Column Names

Reeza — Tue, 07 Dec 2021 01:15:26 GMT

Does this work for you?
Do you need a table or report?

proc freq data=have;
table id*code / out=id_counts norow nocol nopercent sparse;
run;

proc transpose data=id_counts out=want (drop = _name_ _label_);
by id;
id code;
var count;
run;

@cobba wrote:

Using SAS EG v7.1

I have the following data where an event is represented by an ID and any number of codes (always strings) can be applied to each event.
data have;
    input id$ code$ @@;
    datalines;
    aa x1 aa x1 aa x2
    bb x1 bb y1 bb y1 bb z2 bb z4
    cc x1 cc x2 cc y1 cc x1 cc x1 cc q1
    dd z2 dd z2 dd z2 dd q1
    ;
run;
Episode IDs and Codes are always strings but can be of varying lengths.

The data below is what I want. Where the unique codes make up the column headings and the count is performed for each ID.

Re: Count Unique Values by Row and Use Values as Column Names

cobba — Tue, 07 Dec 2021 02:46:32 GMT

That is what I'm looking for. Thank you. How do I make the report a table?

Re: Count Unique Values by Row and Use Values as Column Names

Reeza — Tue, 07 Dec 2021 02:58:40 GMT

It already does that, it's in the WANT data set. Change the name if you want it to have a different name.

@cobba wrote:

That is what I'm looking for. Thank you. How do I make the report a table?