Help using Base SAS procedures

counting with first last

Reply
Contributor
Posts: 28

counting with first last

Hello-

I have typically used the first last statement for unique counting.  I wanted to ask if there was a way to use the first last statemetn for duplicate counting as well?  For example, patientA visited clinic1 clinic2 clinic3 clinic1 clinic4 clinic1 (for a total visit of 6 visits for patientA) in month1 and PatientB visitted clinic2 clinic3 clinic1 clinic2 clinic2 (total 5 visits) in month1 for patientB and PatientC visitted clinic1 clinic3 clinic1 clinic2 clinic2 (total 5 visits) in month1 for patientC.  I want to know the number of visits by month.

So I would have in month1 column

5visits=2 patients

6 visits=1 patient. 

Can I use first last or is there an alternative way to approach this?


Respected Advisor
Posts: 3,156

Re: counting with first last

How does your raw data/ wanted data look like?

Contributor
Posts: 28

Re: counting with first last

Hi-

Data looks like this but at a more grander scale.   The dollar amounts are co-pays but Im not interested in that.  I want to know how many patients made 5 visits to clinics or 6 visits to clinics etc....

Month= January

patientclinic1clinic2clinic3clinic4
A$65
A $125
A $458
A$760
A $1,025
A$2,065
B $456
B $598
B$1,095
B $658
B $354
C$875
C 358
C510
C 450
C 265
Super User
Posts: 5,516

Re: counting with first last

You could use first. and last. variables, if your data were sorted by MONTH PATIENT.  It wouldn't be a bad exercise to try that.  But another important tool to learn is that PROC FREQ is built to count, and can create output data sets.  This report won't be pretty, but it will have the information in it:

proc freq data=have;

   tables month * patient / noprint out=counts;

run;

proc freq data=counts;

   tables month * count;

   label count='Patients with this Number of Visits';

run;

Good luck.


Super User
Posts: 11,343

Re: counting with first last

Posted in reply to Astounding

To add to Astounding's idea, if you don't have the month but have a datevalue associate a format such as MONYY. to do the grouping without having to add additional varialbes.

If you data set is of long enough time you probably want include the year also and the above will take care of it.

Contributor
Posts: 28

Re: counting with first last

Posted in reply to Astounding

Yes I have considered proc freq but i am interested in the number of patients making the total number of clinic visits per month.

In my data example above I would want to know

there were 2 patients visited the clinic 5 times in January and 1 patient visiting 6 clinics in January.

5 clinic visits= 2 patients

6 clinic visits=1 patient

Super User
Posts: 5,516

Re: counting with first last

Yes, that's what the second PROC FREQ gives you.  If you actually have a separate file for each month, but don't have a variable called MONTH, the program becomes easier:

proc freq data=january;

   tables patient / noprint out=counts;

run;

proc freq data=counts;

   tables count;

   title 'Number of Patients with N Visits in January';

run;

Good luck.

Contributor
Posts: 28

Re: counting with first last

Posted in reply to Astounding

Ohh double wow!  Thanks Astounding- thats an even easier approach.   No separate file for each month- so how would I produce the counts by month using the proc freq procedure?


Frequent Contributor
Posts: 87

Re: counting with first last

Wouldn't this work as well?

proc summary noprint nway n

    data = visits ;

            class patient ;

    output out = want (

                    drop = _type_

                    rename = (_freq_ = visits)) ;

run ;

Frequent Contributor
Posts: 87

Re: counting with first last

Could add month easy as well

proc summary noprint nway n

    data = visits ;

            class month patient ;

    output out = want (

                    drop = _type_

                    rename = (_freq_ = visits)) ;

run ;

Contributor
Posts: 28

Re: counting with first last

Thank you for responding.  This one isnt quite what I had in mind.  I did not need each patient displayed.  I need the number of patients and the number of clinics visited each month.   So below would read- two patients made 5 clinic visits and 1 patient made 6 clinic visits.

number of visits          number of patients

5                              2

6                              1


Super User
Posts: 5,516

Re: counting with first last

To include MONTH as a variable, just use the first posted solution.  You might want to play with the format of the output from the second PROC FREQ, such as:

tables month * count / LIST;

Steve, that's just my bias.  I prefer to use PROC FREQ to count and PROC SUMMARY to generate statistics.  But there is an area where their capabilities overlap.  At any rate, you would need a second summarization of your output data set to count how many patients had 5 visits, how many had 6 visits, etc.

Contributor
Posts: 28

Re: counting with first last

Posted in reply to Astounding

Astounding-  I just realized that the frequency unduplicated the counts (counted a clinic only once).  I need every clinic counted for each patient.   Do you think I can still use the frequency procedure? .

Respected Advisor
Posts: 4,934

Re: counting with first last

Yes, it can be done with .last  variables. I assumed the data has the structure you presented :

data visits;
length patient visit $10;
infile datalines missover;
input patient visit @;
do while (not missing(visit));
     output;
     input visit @;
     end;
datalines;
patientA clinic1 clinic2 clinic3 clinic1 clinic4 clinic1
PatientB clinic2 clinic3 clinic1 clinic2 clinic2
PatientC clinic1 clinic3 clinic1 clinic2 clinic2
;


data nbVisits(drop=visit);
do until(last.patient);
     set visits; by patient notsorted;
     nbVisits = sum(nbVisits, 1);
     end;
run;

proc sort data=nbVisits; by nbVisits; run;

data visitStats(drop=patient);
do until(last.nbVisits);
     set nbVisits; by nbVisits;
     nbPatients = sum(nbPatients, 1);
     end;
run;

proc print noobs; run;

PG

PG
Contributor
Posts: 28

Re: counting with first last

Wow!  Thanks PG-  it worked.  So its a two data step part counting until reaching the last record for both steps.  Thank you

Ask a Question
Discussion stats
  • 18 replies
  • 452 views
  • 2 likes
  • 6 in conversation