BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wlierman
Lapis Lazuli | Level 10

I have fifteen fields each representing a CCO (Medicaid health provider)  something like this (the letters indicate that there is a name of the CCO showing up in that field. The coalesce column (Coalesce have) shows what values are shown.  It is okay but in row 4 and 6 two CCOs are displayed.  The reason is that the CCOs offer services across the same zipcodes (splitting the zipcode into respective service areas).

 

Is there a coding approach that will still perform the coalesce (like in column Coalesce have) but for rows with two CCOs show both names in the coalesce field (like in column Coalesce want)?   Then when I do a proc freq by contacts and CCO I would like the double CCOs to show in the freq as C | D and H | I.

 

Thank you for your help again.

 

wklierman

 

 

                                                                                                                                       Coalesce       Coalesce

CCO1         CCO2        CCO3  . . . CCO9     CCO10  .  .  .  CCO14     CCO15                 have               want            

   A                                                                                                                                        A                   A    

                      B                                                                                                                     B                   B

                                                           G                                                                               G                   G

                                        C                                                      D                                          C                   C | D

                                                                          J                                                                  J                    J

                                                          G                                                                                 G                   G

                                                                          H                                       I                         H                   H | I

                       B                                                                                                                    B                    B                                             

1 ACCEPTED SOLUTION
10 REPLIES 10
wlierman
Lapis Lazuli | Level 10

Thank you.  The pipe was only an example. But I want the coalesce as here, but only in the few cases where there is a value in two CCOs for a particular contact would I like the "combined" coalesce value. Thank you

Proc Sql noprint;
CREATE TABLE SASCDC_2.Arias_skinny_contact_for_CCO  AS
   SELECT DISTINCT Contact_Person_ID AS Contact_Person_ID label='Contact_Person_ID',
                  /* Monitoring_Status,
				              Status,
							 County_1,
							 ZIP_Code,
						 Jurisdiction,
						  Designation,*/
      Coalesce (CCO_1, CCO_2, CCO_3, CCO_4, CCO_5, CCO_6, CCO_7,
                CCO_8, CCO_9, CCO_10, CCO_11, CCO_12, CCO_13,
			    CCO_14, CCO_15) AS CCO
   FROM SASCDC_2.Arias_contact_zip_cco_zip
   Order By Contact_Person_ID;
quit;
ballardw
Super User

@wlierman wrote:

Thank you.  The pipe was only an example. But I want the coalesce as here, but only in the few cases where there is a value in two CCOs for a particular contact would I like the "combined" coalesce value. Thank you

 

 What in the data tells us when you might want 2 (or more) values concatenated? Which "few cases"?

Sounds like a CASE statement would be required to do the conditional coalescec / catx.

 

Your example data strongly implies that most of those CCO variables are missing and you were only selecting the ones with values. If that is not the case then your example data should represent your data more clearly. Or if the case is such that the data actually is mostly missing the CATX function will return the only populated variable value. CATX strips trailing blanks from value for the concatenation, so if a variable is missing then it is completely stripped out. The delimited used only ever appears if there are two or more values (of if the single value of the variable happens to be the delimiter chosen).

wlierman
Lapis Lazuli | Level 10
There are 165,000 contacts and most have one CCO that they are assigned to. There are a few (say 100) that have 2 CCOs because as I mentioned because a zipcode will be split between the CCOs. So of the 165K about 135K have a CCO - zipcode or county is missing. This is survey data so much of the data whether REALD or demographic will have some missing or incomplete responses.
So my question is if I use the Coalesce statement in the query do I code a subsetting "if"condition in a data step before the query? I want a condition that catches the 100 or so obs (contacts) that happen to have two CCOs.

Thank you for your help.
wlierman
Lapis Lazuli | Level 10
That should read of 165K overe 135K have a CCO.
ballardw
Super User

@wlierman wrote:
There are 165,000 contacts and most have one CCO that they are assigned to. There are a few (say 100) that have 2 CCOs because as I mentioned because a zipcode will be split between the CCOs. So of the 165K about 135K have a CCO - zipcode or county is missing. This is survey data so much of the data whether REALD or demographic will have some missing or incomplete responses.
So my question is if I use the Coalesce statement in the query do I code a subsetting "if"condition in a data step before the query? I want a condition that catches the 100 or so obs (contacts) that happen to have two CCOs.

Thank you for your help.

Still have described one or more rules that tell us which "contact" might need the 2 CCO. Your example data does not show Zipcodes at all. And now you need to define exactly what is meant by "Zipcode will be split between CCO". Especially since you say Zipcdoes may be missing for up to 75% of the records. That tends to make it a poor decision variable.

 

Coalesce will never return two values. It returns the first non-missing in a list of values. So I don't get why you keep saying coalesce. The CATX function will do that. Have you tested it? You would need to provide more complete data as to why it is not appropriate.

wlierman
Lapis Lazuli | Level 10
With a little bit of follow-up, the catx does the trick.

Thanks again.

wklierman
himself
Pyrite | Level 9
I think you can have

If cmiss(cc:,have) gt 2 then want= catx("|", of cc:);
else want=coalesce(of cc:);
ChrisNZ
Tourmaline | Level 20

I don't understand how @Reeza 's suggestion of using catx() does not answer your need.

wlierman
Lapis Lazuli | Level 10
My question is does that code go into the query or a data step before the query?

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1583 views
  • 1 like
  • 5 in conversation