BookmarkSubscribeRSS Feed
KellyW1117
Calcite | Level 5

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?

8 REPLIES 8
TomKari
Onyx | Level 15

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;

KellyW1117
Calcite | Level 5

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;

Reeza
Super User

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?

KellyW1117
Calcite | Level 5

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

Reeza
Super User

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.

KellyW1117
Calcite | Level 5

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

TomKari
Onyx | Level 15

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

KellyW1117
Calcite | Level 5

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.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 1472 views
  • 0 likes
  • 3 in conversation