topic Re: counting with first last in SAS Procedures

counting with first last

SAS_new — Wed, 05 Jun 2013 19:14:09 GMT

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?

Re: counting with first last

Haikuo — Wed, 05 Jun 2013 19:35:18 GMT

How does your raw data/ wanted data look like?

Re: counting with first last

Astounding — Wed, 05 Jun 2013 19:41:13 GMT

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.

Re: counting with first last

ballardw — Wed, 05 Jun 2013 19:44:36 GMT

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.

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 19:47:35 GMT

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

patient	clinic1	clinic2	clinic3	clinic4
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
C	510
C		450
C		265

Re: counting with first last

PGStats — Wed, 05 Jun 2013 19:55:08 GMT

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;

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 19:55:39 GMT

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

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 20:04:43 GMT

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

Re: counting with first last

Astounding — Wed, 05 Jun 2013 20:11:32 GMT

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.

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 20:18:29 GMT

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?

Re: counting with first last

SteveNZ — Wed, 05 Jun 2013 20:35:34 GMT

Wouldn't this work as well?

proc summary noprint nway n

data = visits ;

class patient ;

output out = want (

drop = _type_

rename = (_freq_ = visits)) ;

run ;

Re: counting with first last

SteveNZ — Wed, 05 Jun 2013 20:40:57 GMT

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 ;

Re: counting with first last

Astounding — Wed, 05 Jun 2013 20:49:48 GMT

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.

Re: counting with first last

Haikuo — Wed, 05 Jun 2013 21:29:43 GMT

If you are only interested in the number of visits and the number of patients who have that visits, then Proc SQL also has approach: (raw data stoled from PG's post):

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

;

proc sql;

select ct as visits, count(*) as patient_num from

(select count(*) as ct from visits group by patient)

group by ct;

quit;

Haikuo

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 21:46:56 GMT

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

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 21:49:41 GMT

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? .

Re: counting with first last

SAS_new — Wed, 05 Jun 2013 21:56:18 GMT

Haikuo-

PROC SQL command seemed to have worked. I gave an example for January, however, I am interested in getting counts for the remaining months. Can you help me with the SQL coding on where I should include the variable 'month' to produce similar counts for each month?

Thank you

Re: counting with first last

Haikuo — Wed, 05 Jun 2013 22:05:32 GMT

proc sql;

select month, ct as visits, count(*) as patient_num from

(select count(*) as ct, month from visits group by month,patient)

group by month,ct;

quit;

Not tested.

Haikuo

Re: counting with first last

SteveNZ — Wed, 05 Jun 2013 22:06:08 GMT

Probably more elegant ways but:

data have;

length month patient visit $10;

infile datalines missover;

input month patient visit @;

do while (not missing(visit));

output;

input visit @;

end;

datalines;

January patientA clinic1 clinic2

January PatientB clinic2 clinic3 clinic1 clinic2 clinic2

January PatientC clinic1 clinic3 clinic1 clinic2 clinic2 clinic3

February patientA clinic1 clinic2 clinic3 clinic1 clinic4 clinic1

February PatientB clinic2 clinic3 clinic1 clinic2 clinic2

February PatientC clinic1 clinic3 clinic1 clinic2 clinic2

;

proc summary noprint nway n

data = have ;

class month patient ;

output out = tmp_want (

drop = _type_

rename = (_freq_ = visits)) ;

run ;

proc summary noprint nway n

data = tmp_want ;

class month visits ;

output out = want (

drop = _type_

rename = (_freq_ = patients)) ;

run ;