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:
ID | CC1 | CC2 | CC3 | CC4 | CC6 | CC11 | CC13 | CC254 |
---|---|---|---|---|---|---|---|---|
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 |
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
CC | CC1 | CC2 | CC6 | CC13 |
---|---|---|---|---|
1 | 200 (100%) | 50 (25%) | 15 | 30 |
2 | 50 (100%) | 3 | 6 | 0 |
6 | 70 | 15 | 12 | 37 |
13 | 56 | 43 | 10 | 1 |
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?
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;
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;
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?
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
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.
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
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.