Dear All,
my question might be too simple but I actually have some trouble figuring it out...I hope I can get some help from you guys, thanks in advance.
data count;
set patent_list;
by permno;
if first.permno=1 then NumCit=0;
NumCit+ncites;
run;
I used the above code to calculated the accumulating number of citation for each company(see the table below). But what I want is only the total number of the citation. (that means I dont need other rows indicating the current accumulated values)
Now I want to create two columns, one is the total number of the patents for each company (permno is the unique identifier, so you can count it until the permno change to different permno, ) and the total number of citations that each company received for its all applied patents.
for example, company D has 10 patents(count till permno changes) and 28 citation in total. So, as a final result, I want to keep all the variables from the table, and add these two new columns.
name | permno | merged_file_date | patnum | fdate | ncites |
A | 11342 | 2/9/2004 | |||
B | 11760 | 3/17/2003 | |||
C | 12320 | 11/30/2004 | |||
D | 44150 | 10/20/2004 | 6412747 | 3/22/2001 | 9 |
D | 44150 | 10/20/2004 | 6412746 | 3/22/2001 | 5 |
D | 44150 | 10/20/2004 | 6468373 | 4/20/2000 | 3 |
D | 44150 | 10/20/2004 | 6405989 | 3/22/2001 | 2 |
D | 44150 | 10/20/2004 | 6405990 | 3/22/2001 | 3 |
D | 44150 | 10/20/2004 | 6554243 | 3/22/2001 | 6 |
D | 44150 | 10/20/2004 | 6905396 | 11/20/2003 | |
D | 44150 | 10/20/2004 | 6874802 | 2/25/2003 | |
D | 44150 | 10/20/2004 | 7000935 | 12/22/2003 | |
D | 44150 | 10/20/2004 | 6949162 | 9/23/2002 | |
E | 47271 | 4/1/2003 | |||
F | 55060 | 5/20/2004 | 6550287 | 6/7/2002 | 0 |
G | 65496 | 6/23/2004 | 6537459 | 4/20/2000 | 11 |
G | 65496 | 6/23/2004 | 6562249 | 7/10/2001 | |
G | 65496 | 6/23/2004 | 6682875 | 6/18/2001 | |
G | 65496 | 6/23/2004 | 6365057 | 11/1/1999 | 3 |
G | 65496 | 6/23/2004 | 6468439 | 11/1/1999 | 9 |
G | 65496 | 6/23/2004 | 6409930 | 11/1/1999 | 0 |
F | 65964 | 4/8/2003 | 5952619 | 10/30/1998 | 8 |
F | 65964 | 4/8/2003 | 5980057 | 9/15/1998 | 11 |
F | 65964 | 4/8/2003 | 6204824 | 9/22/1998 | 4 |
F | 65964 | 4/8/2003 | 6211844 | 1/7/1999 | 3 |
H | 67432 | 3/31/2004 | 6572805 | 10/13/1999 | 0 |
H | 67432 | 3/31/2004 | 6656230 | 10/11/2001 | 0 |
H | 67432 | 3/31/2004 | 6686037 | 12/16/1999 | 1 |
H | 67432 | 3/31/2004 | 6322654 | 10/13/1999 | 3 |
H | 67432 | 3/31/2004 | 6839937 | 9/25/2002 | |
I | 75597 | 5/19/2003 | 6679273 | 7/23/2001 | |
I | 75597 | 5/19/2003 | 6628378 | 11/9/1999 | |
I | 75597 | 5/19/2003 | 6264757 | 7/23/1998 | 4 |
I | 75597 | 5/19/2003 | 6280596 | 10/1/1999 | 2 |
I | 75597 | 5/19/2003 | 6035689 | 6/24/1998 | 3 |
Use the LAST.PERMNO flag to tell which observation to output.
To count the observations just use another counter variable that you increment by 1 each time.
data count;
set patent_list;
by permno;
if first.permno=1 then call missing(NumPat,NumCit);
NumCit+ncites+0;
NumPat+1;
if last.permno;
run;
data logit.count;
set logit.patent_company;
by permno;
if first.permno then Numcit = 0;
numcit + ncites;
if first.permno then
Numpat = 0;
if patnum ne '' then Numpat + 1;
else Numpat + 0;
if last.permno;
run;
hey, thanks for your code. Just made several changes.
I used the above code as I dont want to count patent number if the company doesnt have any patents.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.