topic Re: Creating new variables from a huge text string variable in SAS Programming

Creating new variables from a huge text string variable

sms1891 — Wed, 15 Feb 2023 13:50:27 GMT

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

Re: Creating new variables from a huge text string variable

PaigeMiller — Wed, 15 Feb 2023 14:09:26 GMT

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.

Re: Creating new variables from a huge text string variable

sms1891 — Wed, 15 Feb 2023 14:16:57 GMT

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;

Re: Creating new variables from a huge text string variable

sms1891 — Wed, 15 Feb 2023 14:15:58 GMT

Sorry, the variable name is Shared_services.

Re: Creating new variables from a huge text string variable

SASJedi — Wed, 15 Feb 2023 14:16:38 GMT

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
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
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
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
4	management structure. Best practices.	1	0	0	0	0	0	0	1

Re: Creating new variables from a huge text string variable

PaigeMiller — Wed, 15 Feb 2023 14:18:24 GMT

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

Re: Creating new variables from a huge text string variable

sms1891 — Wed, 15 Feb 2023 14:20:00 GMT

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

Re: Creating new variables from a huge text string variable

PaigeMiller — Wed, 15 Feb 2023 14:22:26 GMT

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

Re: Creating new variables from a huge text string variable

sms1891 — Wed, 15 Feb 2023 14:56:16 GMT

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

Re: Creating new variables from a huge text string variable

sms1891 — Wed, 15 Feb 2023 15:17:24 GMT

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!

Re: Creating new variables from a huge text string variable

Tom — Wed, 15 Feb 2023 15:31:25 GMT

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

Re: Creating new variables from a huge text string variable

SASJedi — Thu, 16 Feb 2023 13:17:32 GMT

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;