BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gobejo
Calcite | Level 5

I have a data set as shown below:

IDfiling number

2115389

2012-09-13

2115389

2011-10-06

2115389

2008-04-09
117654142011-08-08
117654142011-07-11
163161202014-07-23
163161202009-12-18

How would i compute a third colum called COUNT?

IDfiling numbercount

2115389

2012-09-131

2115389

2011-10-062

2115389

2008-04-093
117654142011-08-081
117654142011-07-112
163161202014-07-231
163161202009-12-182
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

If you want the result shown in your last example, it will be a lot easier to do it using a sort and data step. e.g.;

proc sort data=need;

  by 'Duns Number'n 'UCC Filing Dt'n;

run;

data want;

  set need;

  by 'Duns Number'n;

  if first.'Duns Number'n then Count=1;

  else Count+1;

run;

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

proc sql;

  create table WANT as

  select  *,

          COUNT(*) as COUNT

  from    HAVE

  group by ID,

           FIL;

quit;

gobejo
Calcite | Level 5

Hi

This is the way i wrote the code:

PROC SQL;

   CREATE TABLE WORK.NAME AS

   SELECT t1.'IDNumber'n,

          t1.'Business Name'n,

          t1.'Secured Party Name'n,

          t1.'Filing Number'n,

          t1.'Collateral Type'n,

          t1.'Filing Type'n,

          t1.'Filing Dt'n,

    COUNT(*)as Count

      FROM NEED t1

   GROUP BY t1.'ID Number'n,

        t1.'UCC Filing Dt'n

      ORDER BY t1.'ID Number'n,

         t1.'UCC Filing Dt'n DESC;

QUIT;

But i do not get the output like the one above.

Thanks,

Thomas

art297
Opal | Level 21

You select 'IDNumber'n but group and order by 'ID Number',n

Could that be your problem?

gobejo
Calcite | Level 5

Hi Arthur,

Example.png

I have an example showing what the output should look like.

Thanks,

Thomas

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Arthur is correct, you have this line:

   SELECT t1.'IDNumber'n,

Note that there is no space between ID and Number.

   GROUP BY t1.'ID Number'n,

This is with a space.

Then IDNumber and Business Name do not appear in your spreadsheet screenshot.

Then you don't DESC on a group by.

Thirdly I would suggest getting your data out of Excel and into SAS, i.e. a dataset, then working with the dataset.  Excel is not a useful tool for any operation and I would avoid it entirely.

Finally, the principal is that you group by the columns you want to count the distinct values of, so group by and select should be the same.  If thats not what you want, i.e. it looks like you want more columns in the select than the group by I would suggest you subquery the count and left join it back on.  I am out of office now for a few days, so good luck.

art297
Opal | Level 21

If you want the result shown in your last example, it will be a lot easier to do it using a sort and data step. e.g.;

proc sort data=need;

  by 'Duns Number'n 'UCC Filing Dt'n;

run;

data want;

  set need;

  by 'Duns Number'n;

  if first.'Duns Number'n then Count=1;

  else Count+1;

run;

gobejo
Calcite | Level 5

Hi Arthur,

Just a small change

data want;

  set need;

  by 'Duns Number'n;

  if first.'Duns Number'n then Count=0;

  else Count+1;

run;

Thanks,

Thomas

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1110 views
  • 2 likes
  • 3 in conversation