Creating a variable that combines same levels of existing variables SAS 9.4

[Whitlea]

I need to combine same clinic names if the corresponding prior_span =1.

For example, if Clinic_name1 and Clinic_name2 where both Clinic A and Prior_span1 and prior_span2 both =1 then Clinic would = A.

Clinic_name_1 corresponds with Prior_span1

Clinic_name_2 corresponds with Prior_span2

Clinic_name_3 corresponds with Prior_span3

Below is my sample data. 

Thanks!

Data Have;
input 

ID$	Clinic_Name_1$	Clinic_Name_2$	Clinic_Name_3$	Prior_Span1$	Prior_Span2$	Prior_Span3$	Cost service$ ;

datalines;
1	B	A	.	0	0	0	168 A
2	A	A	.	1	0	0	46  B
2	A	A	.	0	1	0	99  C
3	B	.	.	0	0	0	24  D
4	D	.	.	0	0	0	54  A
5	B	D	F	0	1	0	405 B
7	A	.	.	1	0	0	90  F
8	E	E	A	0	0	1	87  J
9	F	.	.	0	0	0	0   G
10	C	C	C	0	0	0	40  C
10	C	C	C	0	0	1	374 B
10	C	C	C	0	1	1	60  A

;

run;




***Summary table-clinic var not created yet****;
proc sql;
create table want as

select 1 as ID1,'service' as a length=40,Clinic,
       1 as ID2,put(service, $40.) as b length=40,
	   1 as ID3,(select count(distinct ID) from have where Clinic=a.Clinic and service=a.service and cost>0 ) as c format=comma20.,
	   1 as ID4,calculated c/(select count(distinct ID) from have where Clinic=a.Clinic and cost>0 ) as d format=percent8.1,
	   1 as ID5,sum(cost) as e format=dollar20.,
	   1 as ID6,sum(cost)/(select sum(cost) from have where Clinic=a.Clinic) as f format=percent8.1

 from have as a

  group by Clinic,service
union all
select 7 as id1,'09'x as a,Clinic,
       7 as id2,'Total Clients' as b,
	   7 as id3,count(distinct ID) as f,
	   7 as id4,.,
	   7 as id5,.,
	   7 as id7,.

 from have
where service ne 'j'  and cost>0
  group by Clinic
union all
select 7 as id1,'09'x as a,Clinic,
       7 as id2,' Total Cost' as f,
	   7 as id3,. as c,
	   7 as id4,.,
	   7 as id5,sum(cost),
	   7 as id7,.

 from have
where service ne 'j'  and cost>0
      group by Clinic
;quit;
options missing=' ';

proc report data=want nowd;

column id1 a b Clinic,(c d e f);
define id1/group noprint;
define a/group noprint ' ';
define b/group ' ';
define c/analysis 'Clients';
define d/analysis '% of Clients';
define e/analysis 'Cost';
define f/analysis '% of Cost';

define Clinic/across '';
compute before a/style={just=l color=black};
if id1=1 then len=0;
 else len=10;
x=' ';
line x $varying10. len;
line a $40.;
endcomp;

run;

[ballardw]

Thank you for providing working data.

Can you provide an example of what the data looks like after the "same levels" are combined for that data?

I am not sure what the output should look like.

You should expand on your single example to include a few more. Such as what are the rules for when you have two (or more) Prior_span =1 and correspond to different clinics such as hypothetical:

15	A	B	C	1	1	1	60  A

what is the rule for selecting the response?

What is the output where there are no 1 in any of the Prior_span

[Whitlea]

Sorry, I did a poor job of explain this. Ultimately, I want to sum clients and cost by Clinic and Service where prior span =1. Here is an Excel example of what I am trying to do in SAS.  I have added ballardw's example to the sample data set.  Hope this helps!

[Tom]

Photographs of a spreadsheet without any explanation do not help much.

What are the RULES?

[mkeintz]

Here is what I think you want.

You apparently want to create a new variable CLINIC, such that
    If 
        all the non-blank values for clinic_name1, clinic_name2, clinic_name3 have the same value,
        and
        all the corresponding prior_span values are "1"

    then
        the new variable gets the common clinic_name value

    otherwise

        it is left blank.

If so, then 

data want (drop=i);
  set have;


  array cn {3} $ clinic_name_1- clinic_name_3;
  array ps {3} $ prior_span1 - prior_span3;

  if prior_span1='1' then clinic=clinic_name_1;

  do i=2 to 3 while (cn{i}^=' ');
    if cn{i}^=cn{1} or ps{i}^='1' then clinic=' ';
  end;
run;

This rule, in the case of your sample data, generates a name for only 1 observation (out of 12).  So I think we may need more clarity from you on the rule to use in assigning a value to CLINIC.

[Ksharp · Accepted solution]

I think the code(wrote by me) you posted has nothing to do with your question.

Data Have;
input 
ID$	Clinic_Name_1$	Clinic_Name_2$	Clinic_Name_3$	Prior_Span1$	Prior_Span2$	Prior_Span3$	Cost service$ ;
datalines;
1	B	A	.	0	0	0	168 A
2	A	A	.	1	0	0	46  B
2	A	A	.	0	1	0	99  C
3	B	.	.	0	0	0	24  D
4	D	.	.	0	0	0	54  A
5	B	D	F	0	1	0	405 B
7	A	.	.	1	0	0	90  F
8	E	E	A	0	0	1	87  J
9	F	.	.	0	0	0	0   G
10	C	C	C	0	0	0	40  C
10	C	C	C	0	0	1	374 B
10	C	C	C	0	1	1	60  A
15  A   B   C   1   1   1   60  A
;
run;
proc sql;
create table classdata as
select * from 
(
select distinct Clinic_Name_1 from have where Clinic_Name_1 is not missing
union
select distinct Clinic_Name_2 from have where Clinic_Name_1 is not missing
union
select distinct Clinic_Name_3 from have where Clinic_Name_1 is not missing
),
(select distinct service from have where service is not missing)
;

create table want as
select Clinic_Name_1,service,count(*) as count,sum(cost) as cost from have where Prior_Span1='1' group by Clinic_Name_1,service
union all
select Clinic_Name_2,service,count(*) as count,sum(cost) as cost from have where Prior_Span2='1' group by Clinic_Name_2,service
union all
select Clinic_Name_3,service,count(*) as count,sum(cost) as cost from have where Prior_Span3='1' group by Clinic_Name_3,service
;
quit;
proc tabulate data=want classdata=classdata;
class service Clinic_Name_1;
var count cost;
table service all,(Clinic_Name_1='Clinic' all)*(count*f=f20.0 cost*f=dollar22.0);
keylabel sum=' ' all='Total';
run;