Desktop productivity for business analysts and programmers

How to create a table with actual data and percentage?

Reply
Contributor
Posts: 21

How to create a table with actual data and percentage?

Hello everyone,

I have asked a similar question earlier

How to create two tables with actual data and percentage displayed in the same table?

Now, that the CCs are not in a range from 1- 177. It consists of a group of random number such as 1, 2, 3, 4, 6, 11, 13, ....., 254

I have come up with a table that flags what CCs each person has.

data test1;

set CC_test (keep=id %CC_LIST ) ;

run;

PROC SQL;

  CREATE VIEW test2 AS

  SELECT SRC.*, "StackedValues" AS _EG_IDCOL_

  FROM test1 AS SRC;

QUIT;

It looks like:

IDCC1CC2CC3CC4CC6CC11CC13CC254
100100101
200000010
1210001000
12300000100
13401000010
14510001001

From this table, I can see that ID1 has CC3, CC11, CC254...

Eventually I would like to count on how many IDs who have CC1 also have CC2 CC3... I want my table to look like

CCCC1CC2CC6CC13
1200 (100%)50 (25%)1530
250 (100%)360
670151237
135643101


From this table, I would be able to see 200 people have CC1. From the CC1 population, 50 also have CC2..15 have CC6..etc. It is more like a matrix table with discontinuous numbers of CCs.

Can anyone let me know how to do it?

PROC Star
Posts: 1,146

Re: How to create a table with actual data and percentage?

Try this out, and see if it's roughly where you're going. I'm not positive that I understand your requirements.

Tom

data have;
   input ID CC1 CC2 CC3 CC4 CC6 CC11 CC13 CC254;
   cards;
1 0 0 1 0 0 1 0 1
2 0 0 0 0 0 0 1 0
12 1 0 0 0 1 0 0 0
123 0 0 0 0 0 1 0 0
134 0 1 0 0 0 0 1 0
145 1 0 0 0 1 0 0 1
run;

PROC TRANSPOSE DATA=Have OUT=TransposedHAVE;
   BY ID;
RUN;

PROC SQL;
   CREATE TABLE ReducedHave AS
      SELECT ID,
         _NAME_
      FROM TransposedHAVE
         WHERE COL1 = 1;
QUIT;

PROC SQL;
   CREATE TABLE CrossedHave AS
      SELECT t1._NAME_ AS Source,
         t2._NAME_ AS Target
      FROM ReducedHave t1
         CROSS JOIN ReducedHave t2
      WHERE t1.ID = t2.ID;
QUIT;

PROC TABULATE
   DATA=CrossedHave;
   CLASS Source / ORDER=UNFORMATTED MISSING;
   CLASS Target / ORDER=UNFORMATTED MISSING;

   TABLE Source, Target * N;
RUN;

Contributor
Posts: 21

Re: How to create a table with actual data and percentage?

Hello Tom,

Thanks for your help. I changed my wording on the question a little bit. Hopefully, this time you can understand my question. My dataset is too large to do the below code. Eventually, I would like to get a CC * CC table to display how many IDs who have CC1 also have CC3, CC4...

data have;

   input ID CC1 CC2 CC3 CC4 CC6 CC11 CC13 CC254;

   cards;

1 0 0 1 0 0 1 0 1

2 0 0 0 0 0 0 1 0

12 1 0 0 0 1 0 0 0

123 0 0 0 0 0 1 0 0

134 0 1 0 0 0 0 1 0

145 1 0 0 0 1 0 0 1

run;

Super User
Posts: 19,167

Re: How to create a table with actual data and percentage?

KellyW1117 wrote:

Now, that the CCs are not in a range from 1- 177. It consists of a group of random number such as 1, 2, 3, 4, 6, 11, 13, ....., 254

Are the CC's the same across row and column or can they be different?

Contributor
Posts: 21

Re: How to create a table with actual data and percentage?

Reeza,

The variables on row and column are the same.

CC   1 2 3 4 6 11 13......254

1

2

3

4

6

11

13

....

254

Super User
Posts: 19,167

Re: How to create a table with actual data and percentage?

I think other than the Data Step1 in the code I provided before the rest would work the same, so it's just a matter of changing that step. I don't have time right now, but perhaps that would help you get started.

Contributor
Posts: 21

Re: How to create a table with actual data and percentage?

Reeza,

Can you let me know if my understanding is correct?

I would like to create macro with the list of the variables. Then merge all the listed macro variables to the existing tables. The rest of the code would be similar to what you helped me previously

PROC Star
Posts: 1,146

Re: How to create a table with actual data and percentage?

Yes, but in your example at the top row 1 (ID = 1) doesn't have a "1" in the CC1 column. I don't understand, if rows and columns are the same?

Tom

Contributor
Posts: 21

Re: How to create a table with actual data and percentage?

Tom,

The Top Row (ID=1) only flags which CCs this person have. In this case, it would be 3, 11, 254. The first table provides all IDs and which CCs each ID has.

The output that I am looking for has nothing to do with ID. I only want to know how many people have CC1. Out of those CC1 population, how many also have CC2, CC3...etc.

Ask a Question
Discussion stats
  • 8 replies
  • 589 views
  • 0 likes
  • 3 in conversation