Creating new variables from a huge text string variable

[sms1891]

Hi experts,

I have a huge string variable Shared_interests (only 19 out of 150 responded to this question. But the response is a huge text string "Other. Shared care management and care coordination system for medical needs Shared infrastructure for social needs services. Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices."

I have to create new variables from this and I tried a few methods (by using like, contains, =:, %) but unsuccessful. I would appreciate your help greatly.

Data a;

Set a;

if Shared_services =: "management structure" then managementn=1; else managementn=0;
if Shared_services =: "physical infrastructure" then physical_infrastructuren=1; else physical_infrastructuren=0;
if Shared_services =: "personnel responsibilities" then personnel_responsibilitiesn=1; else personnel_responsibilitiesn=0;
if Shared_services =: "revenues and expenses" then revenues_and_expensesn=1; else revenues_and_expensesn=0;
if Shared_services =: "technical infrastructure" then technical_infrastructuren=1; else technical_infrastructuren=0;
if Shared_services =: "care management" then care_managementn=1; else care_managementn=0;
if Shared_services =: "social needs services" then social_needs_servicesn=1; else social_needs_servicesn=0;
if Shared_services =: "best practices" then best_practicesn=1; else best_practicesn=0;
if Shared_services =: "Other" then shared_othern=1; else shared_othern=0;

run;

Thank you

[PaigeMiller]

I'm sorry but the question is unclear to me

I have a huge string variable Shared_interests

Do you mean you have a huge string variable named Shared_services?

Exactly what are you trying to do with this huge string variable, you don't really say. You say what you tried that didn't work, but we don't really know what it is that you are looking for. I make a guess: do you want to find if Shared_services CONTAINS "management structure"? Is that right, or is my guess wrong? If that's not right, then please spell out exactly what result you do want.

[sms1891]

Thanks for the quick reply. I am trying to create new variables using the text string variable "Shared_services" . In a data set of 150 observations, 19 replied to this variable 'Shared_services" with text response Other. Shared care management and care coordination system for medical needs Shared infrastructure for social needs services. Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices.

Using this text string response,  I want to create new variables 

if Shared_services =: "management structure" then managementn=1; else managementn=0;
if Shared_services =: "physical infrastructure" then physical_infrastructuren=1; else physical_infrastructuren=0;
if Shared_services =: "personnel responsibilities" then personnel_responsibilitiesn=1; else personnel_responsibilitiesn=0;
if Shared_services =: "revenues and expenses" then revenues_and_expensesn=1; else revenues_and_expensesn=0;
if Shared_services =: "technical infrastructure" then technical_infrastructuren=1; else technical_infrastructuren=0;
if Shared_services =: "care management" then care_managementn=1; else care_managementn=0;
if Shared_services =: "social needs services" then social_needs_servicesn=1; else social_needs_servicesn=0;
if Shared_services =: "best practices" then best_practicesn=1; else best_practicesn=0;
if Shared_services =: "Other" then shared_othern=1; else shared_othern=0;

[sms1891]

Sorry, the variable name is Shared_services. 

[PaigeMiller]

---

@sms1891 wrote:

Thanks for the quick reply. I am trying to create new variables using the text string variable "Shared_services" . In a data set of 150 observations, 19 replied to this variable 'Shared_services" with text response Other. Shared care management and care coordination system for medical needs Shared infrastructure for social needs services. Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices.

---

You said this already, and repeating this does not clear up any of my confusion. When you only tell us what didn't work, we don't really know what you want to do with this long string variable. 

[sms1891]

PaigeMiller, I posted the SAS code in my original post for what I wanted.

[PaigeMiller]

---

@sms1891 wrote:

PaigeMiller, I posted the SAS code in my original post for what I wanted.

---

And that didn't work, you told us that. But we don't know what result would make you happy, you still have not explained. I am not asking for code, I am asking for explanation.

[sms1891]

My code specifies what I wanted. I think SASJedi got it. I will try his code and see if it works. Thank you! 

[Tom]

Let's see if I can translate.

You have a character variable that can have many different substrings included within it (so basically a list of the items selected on a multiple select survey question) 

data have;
   input string $80.;
cards;
item1, item2
item3, item4,item6
item1, item4
;

And you want to generate a series of boolean variables that indicate if specific substrings were included or not. The =: operator you tried only tests if the strings starts with a particular value.  It looks like you would want to search the whole string. So use the FIND() or perhaps FINDW() function instead.

data want;
   set have;
   item1 = 0<findw(string,'item1',' ,.','i');
   item2 = 0<findw(string,'item2',' ,.','i');
   item3 = 0<findw(string,'item3',' ,.','i');
   item4 = 0<findw(string,'item4',' ,.','i');
   item5 = 0<findw(string,'item5',' ,.','i');
   item6 = 0<findw(string,'item6',' ,.','i');
run;

Result

Obs    string                item1    item2    item3    item4    item5    item6

 1     item1, item2            1        1        0        0        0        0
 2     item3, item4,item6      0        0        1        1        0        1
 3     item1, item4            1        0        0        1        0        0

[SASJedi]

The FIND function is your friend here:

data have;
	Shared_services="Other. Shared care management and care coordination system for medical needs Shared infrastructure for social needs services. Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices.";
	output;
	Shared_services="Other. Care management and care coordination system for medical needs Shared infrastructure for social needs services.";
	output;
	Shared_services="Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices.";
	output;
	Shared_services="management structure. Best practices.";
	output;
run;

data want;
   set have;
	managementn=find(lowcase(Shared_services),"management structure")>0;
	physical_infrastructuren=find(lowcase(Shared_services),"physical infrastructure")>0;
	personnel_responsibilitiesn=find(lowcase(Shared_services),"personnel responsibilities")>0;
	revenues_and_expensesn=find(lowcase(Shared_services),"revenues and expenses")>0;
	technical_infrastructuren=find(lowcase(Shared_services),"technical infrastructure")>0;
	care_managementn=find(lowcase(Shared_services),"care management")>0;
	social_needs_servicesn=find(lowcase(Shared_services),"social needs services")>0;
	best_practicesn=find(lowcase(Shared_services),"best practices")>0;
	shared_othern=find(lowcase(Shared_services),"Other")>0;
run;

And - tada!

Obs	Shared_services	managementn	physical_infrastructuren	personnel_responsibilitiesn	revenues_and_expensesn	technical_infrastructuren	care_managementn	social_needs_servicesn	best_practicesn	shared_othern
1	Other. Shared care management and care coordination system for medical needs Shared infrastructure for social needs services. Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices.	1	1	1	1	1	1	1	1	0
2	Other. Care management and care coordination system for medical needs Shared infrastructure for social needs services.	0	0	0	0	0	1	1	0	0
3	Shared management structure. Shared personnel responsibilities. Shared physical infrastructure. Shared revenues and expenses. Shared technical infrastructure including EMR and other IT. Sharing of best practices.	1	1	1	1	1	0	0	1	0
4	management structure. Best practices.	1	0	0	0	0	0	0	1	0

[sms1891]

Hi SASJedi, I think I got everything except the "Other". I want only the ones which says "Other" at the beginning with largecap O and not at other instances where there is a mention of 'other'. Is this possible to filter out for shared_othern?

Thanks!

[SASJedi]

Ah, yes - my mistake, there. The LOWCASE function in the line that detects "Other" is screwing things up.

shared_othern=find(lowcase(Shared_services),"Other")>0;

This would work as you described:

data want;
   set have;
	managementn=find(lowcase(Shared_services),"management structure")>0;
	physical_infrastructuren=find(lowcase(Shared_services),"physical infrastructure")>0;
	personnel_responsibilitiesn=find(lowcase(Shared_services),"personnel responsibilities")>0;
	revenues_and_expensesn=find(lowcase(Shared_services),"revenues and expenses")>0;
	technical_infrastructuren=find(lowcase(Shared_services),"technical infrastructure")>0;
	care_managementn=find(lowcase(Shared_services),"care management")>0;
	social_needs_servicesn=find(lowcase(Shared_services),"social needs services")>0;
	best_practicesn=find(lowcase(Shared_services),"best practices")>0;
	shared_othern=find(Shared_services,"Other")>0;
run;