topic Re: Looking for a string in SAS Programming

Looking for a string

ZosTa — Tue, 16 Oct 2018 11:31:34 GMT

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.

Re: Looking for a string

Kurt_Bremser — Tue, 16 Oct 2018 11:38:52 GMT

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

Re: Looking for a string

RW9 — Tue, 16 Oct 2018 12:11:49 GMT

You could maybe do it with index()

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

Re: Looking for a string

ZosTa — Tue, 16 Oct 2018 12:13:17 GMT

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.

Re: Looking for a string

Kurt_Bremser — Wed, 17 Oct 2018 06:53:27 GMT

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.

Re: Looking for a string

ZosTa — Wed, 17 Oct 2018 08:13:15 GMT

The index worked perfectly, Thank you RW9

Re: Looking for a string

ZosTa — Wed, 17 Oct 2018 08:14:35 GMT

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

Re: Looking for a string

RW9 — Wed, 17 Oct 2018 08:24:27 GMT

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.