topic Re: Transpose three columns dataset in SAS Programming

Transpose three columns dataset

Ramiro_iese — Wed, 08 Mar 2023 11:21:31 GMT

Hi! I have a problem transposing a matrix (this is just a subsample). I need in the columns the cpc, in the rows the wku and the count data as the datapoints. I am trying with this chunk but this is taking a lot of time

data testwkucpc;
input wku count cpc $;
cards;

1 1 Y10T16/3819
1 1 B61C11/04
2 1 C02F1/00
3 1 G01B7/107
5 1 B23B41/04
5 1 B27F5/10
5 1 Y10T408/356
6 1 B27C5/06
7 1 Y10T408/488
7 1 E02B7/42
7 1 Y10S209/933
7 1 E04H17/263
8 1 E04H17/263
8 1 B25D1/16
8 1 B27C5/003
8 1 B27C7/005
;
run;

proc transpose data=testwkucpc out=testwkucpc1;
by wku;
id cpc;
var count;
run;

Re: Transpose three columns dataset

PaigeMiller — Wed, 08 Mar 2023 11:54:55 GMT

How many rows in the full data set? How many distinct values of CPC are there in the full data set?

Why do you need the variable COUNT transposed? What can you do with it in a transposed data set that you can't do in the un-transposed data set?

Re: Transpose three columns dataset

Ramiro_iese — Wed, 08 Mar 2023 12:04:14 GMT

33 millons rows in the entire dataset

9 millons unique cpc

Some wku has several cpc codes, then I need a matrix to compare by rows the cpc between wku

wku/cpc A1 B1 C1

1 1 0 0

2 1 0 1

3 0 1 0

Re: Transpose three columns dataset

PaigeMiller — Wed, 08 Mar 2023 12:39:42 GMT

@Ramiro_iese wrote:

33 millons rows in the entire dataset

9 millons unique cpc

Yes, I suspect that will take a very long time, and your computer might run out of resources.

Some wku has several cpc codes, then I need a matrix to compare by rows the cpc between wku

wku/cpc A1 B1 C1

1 1 0 0

2 1 0 1

3 0 1 0

Please explain this further, and please explain what would you do with this matrix once you have it. Please explain the final goal of this analysis. I suspect the whole thing can be programmed without transposing, if only I knew where you are going.

Re: Transpose three columns dataset

Ramiro_iese — Wed, 08 Mar 2023 12:54:31 GMT

Dear Page, thank you for your response.

Okay. I am trying to take a look at the overlap between patents classification by firm.

wku is the patent number and cpc the patent classification. Most of the time (above 50% of the patents) has more than one cpc assigned. Then, in order to not let aside information, I need to take some similarity measures between patents and then group the patents by firm.

Is it clear enough? Thank you again!

Re: Transpose three columns dataset

PaigeMiller — Wed, 08 Mar 2023 13:19:41 GMT

@Ramiro_iese wrote:

Dear Page, thank you for your response.

Okay. I am trying to take a look at the overlap between patents classification by firm.

wku is the patent number and cpc the patent classification. Most of the time (above 50% of the patents) has more than one cpc assigned. Then, in order to not let aside information, I need to take some similarity measures between patents and then group the patents by firm.

Is it clear enough? Thank you again!

Similarity measures? Such as ...

Firm?