function count with specific restrictions

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

function count with specific restrictions

Hi,

My problem is pretty basic.

I want to create a table with some variables that I take from a bigger table (year, LenderID and (create) a new variable that counts the number of packages by year and by lenderID (Each package is identified with a number. I must count different distinct number package by year and by LenderID)).

My attempt was:

proc sql;

create table newtable as

select distinct year, LenderID, count(distinct package, year, LenderID) as NewVariable,

from table1

group by year LenderID;

quit;

Of course, it doesn't work.

The result wanted looks like that:

yearLenderIDNewVariable
199546633
199646968
199746914
199846788
199946649
200046893
200146946
200246859
200346546
200446540
200546975
200646653
200746593
200846989
200946798
201046537
201146820
201246602
19951071007
19961071142
19971071158
19981071157
19991071002
2000107763
20011071090
2002107656
2003107820
20041071026
2005107676
2006107807
2007107808
2008107784
2009107972
2010107873
20111071013
2012107621

Thank you.


Accepted Solutions
Solution
‎01-08-2014 10:35 AM
Contributor
Posts: 37

Re: function count with specific restrictions

Hi

You can first remove the Duplicate entries by sorting the Data set with Nodups options, then you can do a query over the data set like

proc sql;

create table newtable as

select  year, LenderID, count(*) as NewVariable,

from table1

group by year LenderID;

quit;

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: function count with specific restrictions

Not a lot different than the code you wrote:

proc sql;

  create table newtable as

    select year, LenderID, count(distinct package) as NewVariable

      from table1

        group by year,LenderID

          order by LenderID,year

  ;

quit;

Solution
‎01-08-2014 10:35 AM
Contributor
Posts: 37

Re: function count with specific restrictions

Hi

You can first remove the Duplicate entries by sorting the Data set with Nodups options, then you can do a query over the data set like

proc sql;

create table newtable as

select  year, LenderID, count(*) as NewVariable,

from table1

group by year LenderID;

quit;

Contributor
Posts: 62

Re: function count with specific restrictions

Thank you!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 232 views
  • 3 likes
  • 3 in conversation