CODEWITHSUNDEEP
sasproc
Kelly Wu
Kelly Wu

Reputation: 29

Two Way Transpose SAS Table

I am trying to create a two way transposed table. The original table I have looks like

id      cc
1       2
1       5
1       40
2       55
2       2
2       130
2       177
3       20
3       55
3       40
4       30
4       100

I am trying to create a table that looks like

CC        CC1     CC2…  …CC177
1         264     5        0
2         0      132       6
…
…
177       2        1      692

In other words, how many id have cc1 also have cc2..cc177..etc

The number under ID is not count; an ID could range from 3 digits to 5 digits ID or with numbers such as 122345ab78

Is it possible to have percentage display next to each other? 

    CC        CC1 %    CC2    %…  …CC177
1          264 100%   5 1.9%        0
2           0         132           6
…
…
177         2              1       692

If I want to change the CC1 CC2 to characters, how do I modify the arrays? Eventually, I would like my table looks like

CC     Dell Lenovo HP Sony
Dell
Lenovo
HP
Sony

The order of the names must match the CC number I provided above. CC1=Dell CC2=Lenovo, etc. I would also want to add percentage to the matrice. If Dell X Dell = 100 and Dell X Lenovo = 25, then Dell X Lenovo = 25%.

Upvotes: 0

Views: 268

Answers (1)

Reeza
Reeza

Reputation: 21264

This changes your data structure to a wide format with an indicator for each value of CC and then uses proc corr (correlation) to create the summary table.

Proc Corr will generate the SCCP - which is the uncorrected sum of squares and crossproducts. It's something that's related to correlation, but the gist is it creates the table you're looking for. The table is output in the SAS results window and the ODS OUTPUT statement will capture the table in a dataset called coocs. 

data temp;
set have;
by ID;
retain CC1-CC177;
array CC_List(177) CC1-CC177;

if first.ID then do i=1 to 177;
   CC_LIST(i)=0;
end;

CC_List(CC)=1;

if last.ID then output;

run;


ods output sscp=coocs;
ods select sscp;
proc corr data=temp sscp;
var CC1-CC177;
run;
proc print data=coocs;
run;

Here's another answer, but it's inefficient and has it's issues. For one, if a value is not anywhere in the list it will not show up in the results, i.e. if there is no 20 in the dataset there will be no 20 in the final data. Also, the variables are out of order in the final dataset.

proc sql;
create table bigger as
select a.id, catt("CC", a.cc) as cc1, catt("CC", b.cc) as cc2
from have as a
cross join have as b
where a.id=b.id;
quit;

proc freq data=bigger noprint;
table cc1*cc2/ list out=bigger2;
run;

proc transpose data=bigger2 out=want2;
by cc1;
var count;
id cc2;
run;

Upvotes: 4

Related Questions