Help using Base SAS procedures

Can I count non-numeric field in proc SQL ?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Can I count non-numeric field in proc SQL ?

I have insurance claim database. I want to know from which hospital I got maximum claims. How can I do this in PROC SQL procedure?

Thanks in advance.

Arun


Accepted Solutions
Solution
‎03-27-2012 09:33 AM
Respected Advisor
Posts: 3,156

Can I count non-numeric field in proc SQL ?

Linlin,

Count(*) will count all of the rows. In the cases when only populated fields need be counted, the variable names will be needed:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

;

proc sql ;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

 

  quit;

Haikuo

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Can I count non-numeric field in proc SQL ?

Posted in reply to Aquarian27

Can you post some sample/fake data that we can have a taste of it?

Occasional Contributor
Posts: 9

Can I count non-numeric field in proc SQL ?

Here is sample data for Claim table:

BenefUserIDClmAmountApproveAmountStatusNameTreatmentNameHospId
1082013317121SettledSurgical ManagementHopID-846
1143700028080SettledCataractHopID-490
1174000040000SettledSurgical ManagementHopID-32
141140780CancelledCataractHopID-766
1471683715637SettledSurgical ManagementHopID-490
1481205311953SettledMedical Management ( Conservartive Management)HopID-846
1595000020000SettledSurgical ManagementHopID-499
1862123116000SettledMedical Management ( Conservartive Management)HopID-490

  1. Which hospital has got more number of patients?
Respected Advisor
Posts: 3,156

Can I count non-numeric field in proc SQL ?

Posted in reply to Aquarian27

If one row is equivalent to one patient, then linlin's solution is good for you. if you only care the number of different patient, the use: count (distinct BenefUserID)

Regards,

Haikuo

Super Contributor
Posts: 1,636

Re: Can I count non-numeric field in proc SQL ?

Posted in reply to Aquarian27

do you want something like below?

data have;

input hospital $ claim;

cards;

a 23

b 45

a 56

b 43

a 55

;

proc sql outobs=1;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

        

  quit;

updated after Doc Muhlbaier and Haikuo's comments.  Thank you!

Trusted Advisor
Posts: 2,116

Can I count non-numeric field in proc SQL ?

Linlin -- slick!  Learned something new; good way to start the day.

One correction, no parentheses around the OUTOBS=1 option.

Solution
‎03-27-2012 09:33 AM
Respected Advisor
Posts: 3,156

Can I count non-numeric field in proc SQL ?

Linlin,

Count(*) will count all of the rows. In the cases when only populated fields need be counted, the variable names will be needed:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

;

proc sql ;

      select

     hospital,count(claim) as count

             from have

                  group by hospital

                            order by count desc;

 

  quit;

Haikuo

Frequent Contributor
Frequent Contributor
Posts: 94

Can I count non-numeric field in proc SQL ?

A nice way of limiting the output - looks like it'll be a handy workaround for the lack of "top" in SAS's SQL implementation.

One thing worth noting though is that in the event of a tie it would only print out the first.  For example if two hopitals both had 50 claims, only the first would be output - presumably this is determined by input data order, but I guess we'd have to take it as arbitrary.

PROC Star
Posts: 7,485

Can I count non-numeric field in proc SQL ?

DF: While I, too, learned something new from Linlin's post, I agree that neither of the suggestions address ties.  How about:

data have;

input hospital $ claim;

cards;

a 23

b 45

a .

b 43

a 55

c 55

;

proc sql ;

  select hospital from

    (select hospital,count(claim) as counts

       from have

         group by hospital)

           having counts=max(counts)

   ;

quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 1361 views
  • 6 likes
  • 6 in conversation