topic Re: Generate Crosstab in SAS in SAS Programming

Generate Crosstab in SAS

pmpradhan — Mon, 23 Oct 2017 15:14:07 GMT

I have a data that looks like as follows:

NAME	DOCTOR	NURSE	ASSISTANT_PHYSICIAN	CONSULTATION	SURGERY	PRESCRIPTION	CONSULTATION	PROCEDURE
Healing Hospital	4	0	3	1	1	0	8	0
Helping Hospital	.	1	9	0	.	3	0	1
Caring Hospital	3	5	.	1	4	0	1	.
Treatment Hospital	2	8	5	.	0	1	0	9

I want to create a cross-tab that looks like the following:

	CONSULTATION	SURGERY	PRESCRIPTION	CONSULTATION	PROCEDURE
DOCTOR
NURSE
ASSISTANT_PHYSICIAN

Please help! Any advice are welcomed!

Re: Generate Crosstab in SAS

andreas_lds — Mon, 23 Oct 2017 15:42:26 GMT

You have to describe the logic to be used to transform your data to the requested output-format. And you should post data in a format that we can use in sas code without any additional work => post data as data-step using datalines statement.

The column "CONSULTATION" exists twice in your data => this is not possible in a dataset.

Re: Generate Crosstab in SAS

pmpradhan — Mon, 23 Oct 2017 16:01:08 GMT

Hi Andreas,

I have revised the question and I'm also including datalines statement for quick recreation of data in SAS.

data test;
input name $ Doctor Nurse Assistant_physician Consultation Surgery Prescription Procedure;
datalines;
Healing_Hospital 4 0 3 1 1 0 0
Helping_Hospital . 1 9 0 . 3 1
Caring_Hospital 3 5 . 1 4 0 .
Treatment_Hospital 2 8 5 . 0 1 9
;
run;

I want the output table as

	CONSULTATION	SURGERY	PRESCRIPTION	PROCEDURE
DOCTOR
NURSE
ASSISTANT_PHYSICIAN

Thank you!

Re: Generate Crosstab in SAS

PaigeMiller — Mon, 23 Oct 2017 16:35:18 GMT

You said you want crosstabs in a particular formatted table.

Are the numbers in the tables the SUM of all values that go into that table? Or is it the average? Or is it something else?

Since there are multiple nurses in the data, yet you have one row for NURSES, does that mean we just average or sum the number of consultations across all nurses? Why would that be different than the average or sum across all doctors? Why would that be different than the average or sum across all physician assistants?

In other words, it's still very unclear to me how to go from the input data to the final table.

Re: Generate Crosstab in SAS

ballardw — Mon, 23 Oct 2017 16:46:10 GMT

Is this supposed to be a data set for the result or is this a report (to be read by people?)

And you should provide what kind of numbers go in the result as it isn't obvious

Re: Generate Crosstab in SAS

pmpradhan — Mon, 23 Oct 2017 16:53:51 GMT

The numbers in the result table should have SUM of all the values in the dataset.

Re: Generate Crosstab in SAS

pmpradhan — Mon, 23 Oct 2017 16:54:38 GMT

This is a report. The numbers should be SUM from the given dataset.

Re: Generate Crosstab in SAS

PaigeMiller — Mon, 23 Oct 2017 17:53:44 GMT

Okay, it's the SUM.

Now please explain the logic used to obtain the values in the table, as I asked:

Since there are multiple nurses in the data, yet you have one row for NURSES, does that mean we just sum the number of consultations across all nurses? Why would that be different than the sum across all doctors? Why would that be different than the sum across all physician assistants?

In other words, it's still very unclear to me how to go from the input data to the final table.

Re: Generate Crosstab in SAS

pmpradhan — Mon, 23 Oct 2017 18:45:40 GMT

Apologies, I forgot to answer the latter question.

Since there are multiple nurses in the data, yet you have one row for NURSES, does that mean we just sum the number of consultations across all nurses? Yes

Why would that be different than the sum across all doctors?

Why would that be different than the sum across all physician assistants?

The only reason I can think of is because the total no of nurses, doctors and physician assistants is different.

If you are asking that if there is a way to attribute each consultation, surgery types of care to nurses and doctors? then, I don't think there is a way to distinct that which is why wanted to hear experts opinion. 🙂

Re: Generate Crosstab in SAS

ballardw — Mon, 23 Oct 2017 20:05:02 GMT

@pmpradhan wrote:

Hi Andreas,

I have revised the question and I'm also including datalines statement for quick recreation of data in SAS.

data test;
input name $ Doctor Nurse Assistant_physician Consultation Surgery Prescription Procedure;
datalines;
Healing_Hospital 4 0 3 1 1 0 0
Helping_Hospital . 1 9 0 . 3 1
Caring_Hospital 3 5 . 1 4 0 .
Treatment_Hospital 2 8 5 . 0 1 9
;
run;

I want the output table as

CONSULTATION SURGERY PRESCRIPTION PROCEDURE

DOCTOR ? ? ? ?

NURSE ? ? ? ?

ASSISTANT_PHYSICIAN ? ? ? ?

Thank you!

Given your example data what number should go where the question marks are above?

I actually do not see any obvious connection in the source data that let me say anything about an intersection of Doctor and Consultation

Re: Generate Crosstab in SAS

pmpradhan — Mon, 23 Oct 2017 20:42:51 GMT

Exactly that was where I was confused as I well.

Do you think a cross tab of such data could be done or the data lacks enough details to make it a cross tab?

Re: Generate Crosstab in SAS

PaigeMiller — Mon, 23 Oct 2017 21:57:00 GMT

@pmpradhan wrote:

Exactly that was where I was confused as I well.

Do you think a cross tab of such data could be done or the data lacks enough details to make it a cross tab?

It is entirely up to you what to do with the data. It's your problem. You have to decide, not us. None of us here understand the problem.

This is no longer a SAS problem, but a problem of defining what needs to be done.

Re: Generate Crosstab in SAS

mkeintz — Mon, 23 Oct 2017 22:02:20 GMT

Then, from the sample data you provided, manually fill in sample values in the cell question marks in @ballardw's sample table. We'll be better able to understand the rules you have in mind.

Re: Generate Crosstab in SAS

PaigeMiller — Tue, 24 Oct 2017 01:01:47 GMT

@mkeintz wrote:

Then, from the sample data you provided, manually fill in sample values in the cell question marks in @ballardw's sample table. We'll be better able to understand the rules you have in mind.

No, that's not sufficient. He needs to fill in the table as you said, and then EXPLAIN how he got those numbers in those cells.

@pmpradhan we have been asking you to give us the details, but this has been a series of asking for more information about this problem, and you not providing it. We shouldn't have to ask for details repeatedly. Explain!

Re: Generate Crosstab in SAS

pmpradhan — Tue, 24 Oct 2017 13:32:08 GMT

@mkeintzThank you for your kind suggestion.

Here is the data table:

NAME	DOCTOR	NURSE	ASSISTANT_PHYSICIAN	CONSULTATION	SURGERY	PRESCRIPTION	PROCEDURE
Healing Hospital	4	0	3	1	1	0	0
Helping Hospital	.	1	9	0	.	3	1
Caring Hospital	3	5	.	1	4	0	.
Treatment Hospital	2	8	5	.	0	1	9
Total	9	14	17	2	5	4	10

The result cross tab:

	CONSULTATION	SURGERY	PRESCRIPTION	PROCEDURE	Total
DOCTOR	11(i.e. total doctor 9+ total consultation 2)	14(i.e. total doctor 9+ total surgery 5)	13 (i.e. total doctor 9+ total prescription 4)	19 (i.e. total doctor 9+ total procedure 10)	57
NURSE	16 (i.e. total nurse 14+ total consultation 2)	19 (i.e. total nurse 14+ total surgery 5)	18 (i.e. total nurse 14+ total prescription 4)	24 (i.e. total nurse 14+ total procedure 10)	77
ASSISTANT_PHYSICIAN	19 (i.e. total assistant_physician 17+ total consultation 2)	22 (i.e. total assistant_physician 17+ total surgery 5)	21 (i.e. total assistant_physician 17+ total prescription 4)	27 (i.e. total assistant_physician 17+ total procedure 10)	89
Total	46	55	52	70	223

Can you please help me generate this output. Thank you! 🙂

Re: Generate Crosstab in SAS

mkeintz — Tue, 24 Oct 2017 16:34:17 GMT

You have three variables identifying three provider types, and four variables identifying four service types. So imagine you read in each of your current observations, and making twelve observations from it (corresponding to 3 rows and 4 columns). The resulting data set, NEED, could have these 3 variables:

Provider_type (with values "Doctor", "Nurse", "Assistant_Physician") (i.e. the var name of the indicated provider variable)
Service_type ("Consultation .... Procedure") - var name of the indicated service variable
MY_FREQ (the sum of values of the indicated provider and service

So you would 12 times as many observations as you start with. But you could now do a crosstab of provider_type by service_type, and tell the sas procedure to weight each record by MY_FREQ.

Something like

proc tabulate data=need noseps order=data;
  freq my_freq;
  tables
     provide_type  all='Total' ,
     service_type * N * f=12.0;
run;

So now, you need to determine how to create data set NEED. Consider using two arrays:

An array for rows (variables doctor, nurse, assistant_physician
An array for cols (consultation ... procedure)

You can do nested loops, loop through the column array inside a loop over the row array. Set MY_FREQ to the sum of values for the row variable and the col variable, and then OUTPUT.