Looking for a string

[ZosTa]

Please help:

Using version 9.3 I have a dataset which is a list of NPO's around South Africa and is named NPOz. From the 26 variables one is named Address and as the name suggests it contains the physical addresses for all the observed NPO's. From the address variable, I want to extract the name of the city so as to keep those that are from Johannesburg and Pretoria and delete the rest. Please help me with the right code for this job.

[Kurt_Bremser]

---

@ZosTa wrote:

Please help:

Using version 9.3 I have a dataset which is a list of NPO's around South Africa and is named NPOz. From the 26 variables one is named Address and as the name suggests it contains the physical addresses for all the observed NPO's. From the address variable, I want to extract the name of the city so as to keep those that are from Johannesburg and Pretoria and delete the rest. Please help me with the right code for this job.

---

Please post example data.

Use a data step with datalines, so we can easily recreate your dataset.

[ZosTa]

data NPOz; *[1]*
input ID name source_url status category fax postal_address
physical_address phone $reg_no $reg_date email_address category1
category2 category3 ;

datalines;

143.655 Umusa_Wenkosi_Gospel_Ministries www.npo.gov.za Registered
Development_and_Housing 113354483 P\B_X28_Johannesburg_2006
"33Church_Street""Alberton""Johannesburg""2006" ..............

156.666 Mcmillan_House_of_Care www.npo.gov.za Application_Declined
Health_Care 124568895 ...........................

;

run;

I tried my best to recreate the data in the data step but the actual
data set is imported from an excel spreadsheet.

[Kurt_Bremser]

With Excel, you have the problem that you need to rely on guessing procedures when importing.

Save the Excel data to a csv file, and post some sample lines (don't forget to anonymize if sensitive data is present) in a window opened with the {i} button, so we can see the file as-is. From that it will be possible to create a data step to read the csv.

[RW9]

You could maybe do it with index()

where index(upcase(address),"JOHANNESBURG") or index(upcase(address),"PRETORIA");

[ZosTa]

The index worked perfectly, Thank you RW9

[ZosTa]

Thank you everyone for your assistance, I used the index statement from RW9 and it works perfectly.

[RW9]

No worries.  Just bear in mind that it is an exact match, miss-spellings for instance wouldn't get picked up.  For that you would need a more complex algorithm, maybe using Regular Expressions, or some other pattern matching.