topic Re: Delete observations which include certain text in one of many variables in SAS Programming

Delete observations which include certain text in one of many variables

mgrasmussen — Tue, 10 Aug 2021 12:00:21 GMT

Dear SAS experts

I have approximately 30 variables in a dataset which are all character variables. Many of them contain no data (are blank), but the rest contain some text. I would like to delete all observations which include certain data (text) among these 30 variables.

For example in a dataset which include data on automobile (brand) ownership, I might want to all those persons/observations who own a "Toyota" or a "Honda". This fictional dataset may include 20 variables containing information on automobile ownership, i.e. a person may in this case own up to 20 cars. But I would like to delete those observations who own either a "Toyota" or a "Honda" (and of course then also those who own both), regardsless of the brands of cars they may own in addition to these brands.

I have tried different syntax but I cannot get it to work. Can anyone help with some generic code? I suspect that I need to run some syntax in combination with some form of "first variable-last variable in dataset" specification.

Thank you

Best regards

Re: Delete observations which include certain text in one of many variables

tarheel13 — Tue, 10 Aug 2021 12:13:22 GMT

I think you should make an array and loop through it. Create a flag for the if it's Toyota or Honda. Then simply write if flag='Y' then delete in a DATA step.

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Tue, 10 Aug 2021 12:22:34 GMT

Dear Irackley

Thank you for the advice.

I am somewhat new to SAS and have only briefly heart about arrays. I will look more into this.

Re: Delete observations which include certain text in one of many variables

tarheel13 — Tue, 10 Aug 2021 15:40:22 GMT

ok, I don't know what your variables are named but pretend it's var1-var30. Then you can write code like this.

data mydata;
set cars;
flag='N';
array cars[30] $ var1-var30;
do i=1 to dim(cars);
if cars[i] in ('Toyota', 'Honda') then flag='Y';
end;
if flag='Y' then delete;
run;

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Tue, 10 Aug 2021 12:46:14 GMT

Looks great. Thanks!

What does "if cars[I]" reference? I suspect it has something to do with the do loop. Perhaps there is no case sensitivity here? i (small i)=I (large i). I suspect that this is the case.

Best regards

Re: Delete observations which include certain text in one of many variables

tarheel13 — Tue, 10 Aug 2021 13:19:15 GMT

try it out and let me know if it works. I wanted to leave it lowercase but my laptop is automatically capitalizing it. I declared a character array called cars for your 30 variables or maybe you said 20. it is going to loop through each element in the array. I is the iterator. it's referencing elements in the array. dim(cars) is the all the values in the array or if you don't want to do that just put I=1 to 30 if you know there are 30 elements in the array.

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Tue, 10 Aug 2021 14:03:13 GMT

Once again thanks.

The only challenge I have left is that your example assumes that my variables are numbered 1-30 (at the end of each variable name). They are not. Is there not a more flexible way of approaching this problem where one is not reliant on numbers, but where one can define a variable list to loop over?

Thanks

Re: Delete observations which include certain text in one of many variables

tarheel13 — Tue, 10 Aug 2021 14:24:30 GMT

No. You will have to write the names of the variables to include in the array.

Re: Delete observations which include certain text in one of many variables

ballardw — Tue, 10 Aug 2021 14:26:04 GMT

@mgrasmussen wrote:
Once again thanks.

The only challenge I have left is that your example assumes that my variables are numbered 1-30 (at the end of each variable name). They are not. Is there not a more flexible way of approaching this problem where one is not reliant on numbers, but where one can define a variable list to loop over?

Thanks

In the bit

array cars[30] $ var1-var30;

you can replace the var1-var30 with any list of your variables. You don't even have to have the number 30 if the variables already exist in the data set, use array (*) and the size of the array is set by the number of variables you supply.

array somename(*) thisvar thatvar anothervar a b c pdq;

for example would create the array Somename and the variables listed would be the members.

Read the documentation for the Array statement. The variables must be of the same type.

Somename[1] references the first variable named: Thisvar; Somename[2] would be Thatvar and so on.

Any place your code might want to use the variable values you can use either the array reference, Somename[] or the name. The value used in the brackets must be an integer and unless you use some different options for establishing the array have a value fo 1 to n, where n is the number of variables.

Re: Delete observations which include certain text in one of many variables

PaigeMiller — Tue, 10 Aug 2021 14:31:46 GMT

@mgrasmussen wrote:
The only challenge I have left is that your example assumes that my variables are numbered 1-30 (at the end of each variable name). They are not. Is there not a more flexible way of approaching this problem where one is not reliant on numbers, but where one can define a variable list to loop over?

You can use variable lists, for example, if the variables are consecutive in the SAS data set, and the first variable is named population and the last of the consecutive variables is named avg_income, you can use something like this:

array x population--avg_income;

But enough of us guessing ... @mgrasmussen its time for you to be specific, and show us (a portion of) your actual variable names.

Re: Delete observations which include certain text in one of many variables

jimbarbour — Tue, 10 Aug 2021 14:35:23 GMT

It would be helpful if you post some data -- even if it's just mocked up data.

One question I have is: Can "Toyota" or "Honda" appear within other text or is the brand name of the car in a column by itself?

For example, would your data look more like the "Brand" column below or would it look more like the "Car_Info" column below?

Brand	Car_Info
Toyota	2014 Toyota Camry

If "Toyota" can appear anywhere in the column, then we need to take a different approach.

Jim

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Tue, 10 Aug 2021 15:16:56 GMT

Hey PaigeMiller

This sounds like something which could be a solution. My only question is how you then “call” such a list (“x” according to your example) in a SAS do loop?

Thank you

Re: Delete observations which include certain text in one of many variables

PaigeMiller — Tue, 10 Aug 2021 15:18:23 GMT

Same way you created and used an ARRAY previously (as in the example code from @tarheel13 )

Re: Delete observations which include certain text in one of many variables

ErikLund_Jensen — Tue, 10 Aug 2021 17:06:31 GMT

Hi @mgrasmussen

Here is a slightly modified version of @tarheel13 's code. It uses an automatic array _characters_ containing all defined character variables (a smart SAS feature). The index function + lowcase identifies all occurences of Toyota or Honda in all character strings regardless of case or other content in the text.

data mydata;
	set sashelp.cars;
	array cars $ _character_;
	flag='N';
	do i=1 to dim(cars);
		if index(lowcase(cars[i]),'toyota') or index(lowcase(cars[i]),'honda') then flag='Y';
	end;
	if flag='Y' then delete;
run;

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Thu, 12 Aug 2021 07:29:01 GMT

Dear Irackley

The solution I found makes most sense it based on most of your code, but with small changes here and there.

My proposed solution can be written in a general way as:

data new;
set old;
flag='N';
array delete $ firstvar--last var; /* There are approximately 30 variables (consecutive), which are all character variables */
do i=1 to dim(delete);
if (delete(i)='nametype1' or delete(i)='nametype2') then flag='Y';
end;
if flag='Y' then delete;
drop flag i;
run;

I realize that there maybe even smarter ways of writing the code but I have written it in this way so it more understable for both myself and my colleagues.

Once again thanks for your help!

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Thu, 12 Aug 2021 07:32:45 GMT

Dear Ballardw

Thanks for the input. But if you have 70 variables the code would be quite long would and not that helpful, correct? As far as I understand your code makes sense mostly when you have relatively few variables which you want to loop over. But it does work around the problem of having to rely on numbers.

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Thu, 12 Aug 2021 07:34:53 GMT

It works perfect. Thanks.

Normally I would supply a code example using some available data, but I could not find one which was readily available. Fortunately I got the help I needed using generic code.

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Thu, 12 Aug 2021 07:37:24 GMT

Dear Jim

Thanks for the input.

I realize that I should have specified this detail. The data is as Brand, but at some point I will need to find similar solutions for variables which are organised as 'Car_Info'. I believe another person posted code which would work in such a scenario (with 'Car_info').

Re: Delete observations which include certain text in one of many variables

mgrasmussen — Thu, 12 Aug 2021 08:05:11 GMT

Dear Erik

Thanks. These are great features and would work in my case.

What is the purpose of the lowcase function? I believe I understand what it does, but why is it necessary here? I would suspect that this specification is related to the fact that you write 'toyota' and 'honda' within the code in brackets.

Re: Delete observations which include certain text in one of many variables

tarheel13 — Fri, 13 Aug 2021 17:44:30 GMT

Lowcase function is more defensive programming in case your variables were not all in the same case. Using this function will capture Toyota or Honda regardless of case