topic Re: Issue when using FINDW to KEEP observations in SAS Programming

Issue when using FINDW to KEEP observations

Phil_NZ — Wed, 03 Mar 2021 03:08:27 GMT

Hi SAS Users,

Normally, I use the FINDW to delete the observation that the associated variable in the array. For example, I deal with sashelp.class

The whole dataset is

Name	Sex	Age	Height	Weight
Alfred	M	14	69	    112.5
Alice	F	13	56.5	84
Barbara	F	13	65.3	98
Carol	F	14	62.8	102.5
Henry	M	14	63.5	102.5
James	M	12	57.3	83
Jane	F	12	59.8	84.5
Janet	F	15	62.5	112.5
Jeffrey	M	13	62.5	84
John	M	12	59	    99.5
Joyce	F	11	51.3	50.5
Judy	F	14	64.3	90
Louise	F	12	56.3	77
Mary	F	15	66.5	112
Philip	M	16	72	    150
Robert	M	12	64.8	128
Ronald	M	15	67	    133
Thomas	M	11	57.5	85
William	M	15	66.5	112

When I want to exclude observations that have name 'John','Philip','Robert', I have the code as below

data abc;
	set sashelp.class;
		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
		do i= 1 to dim(del_name);
		if findw(Name,del_name[i],'','eir')>0 then delete;
   end;
run;

And it works:

Name	Sex	Age	Height	Weight	i
Alfred	M	14	69	    112.5	4
Alice	F	13	56.5	84	    4
Barbara	F	13	65.3	98	    4
Carol	F	14	62.8	102.5	4
Henry	M	14	63.5	102.5	4
James	M	12	57.3	83	    4
Jane	F	12	59.8	84.5	4
Janet	F	15	62.5	112.5	4
Jeffrey	M	13	62.5	84	    4
Joyce	F	11	51.3	50.5	4
Judy	F	14	64.3	90	    4
Louise	F	12	56.3	77	    4
Mary	F	15	66.5	112	    4
Ronald	M	15	67	    133	    4
Thomas	M	11	57.5	85	    4
William	M	15	66.5	112	    4

HOWEVER, when I try to do the opposite thing, keeping observations that have the name 'John','Philip','Robert', I run the code below

data abc;
	set sashelp.class;
		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
		do i= 1 to dim(del_name);
		if findw(Name,del_name[i],'','eir')=0 then delete;
		Comment this is the only line I changed;
   end;
run;

The result is as below

with the log is

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.ABC has 0 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Could you please let me know what I did wrong in this code (I must use an array here because my real case is much more complex) ?

Warm regards.

Re: Issue when using FINDW to KEEP observations

SASKiwi — Wed, 03 Mar 2021 03:36:25 GMT

In your second example, for a row to be kept, John AND Philip AND Robert must be ALL be present. No rows meet this condition.

Re: Issue when using FINDW to KEEP observations

SASKiwi — Wed, 03 Mar 2021 03:43:12 GMT

This will get you what you want - note the use of the implicit boolean condition:

data abc;
	set sashelp.class;
		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
		do i= 1 to dim(del_name);
		if findw(Name,del_name[i],'','eir');
		Comment this is the only line I changed;
   end;
run;

Re: Issue when using FINDW to KEEP observations

Phil_NZ — Wed, 03 Mar 2021 03:50:48 GMT

Hi @SASKiwi

Thank you for yoru explanation, you are right about the logical line.

Regarding the code, unfortunately, I copy the code to my SAS and it still result in blank

28         data abc;
29         	set sashelp.class;
30         		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
31         		do i= 1 to dim(del_name);
32         		if findw(Name,del_name[i],'','eir');
33         		Comment this is the only line I changed;
34            end;
35         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.ABC has 0 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

result:

Warm regards.

Re: Issue when using FINDW to KEEP observations

mkeintz — Wed, 03 Mar 2021 03:57:57 GMT

The subsetting IF in the do loop doesn't do what you need. Instead the IF test should have a "then output" action:

data abc;
	set sashelp.class;
		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
		do i= 1 to dim(del_name);
		if findw(Name,del_name[i],'','eir') then output;
		Comment this is the only line I changed;
   end;
run;

Most observations will fail all 3 if tests. But 3 of them will pass once each.

Re: Issue when using FINDW to KEEP observations

PGStats — Wed, 03 Mar 2021 03:58:06 GMT

You could also replace the loop with

if propcase(name) in del_name;

to keep, or

if propcase(name) in del_name then delete;

to drop the names listed in the temp array.

Re: Issue when using FINDW to KEEP observations

Phil_NZ — Wed, 03 Mar 2021 04:21:08 GMT

Hi @mkeintz

It is very interesting, I need to admit that I was very close to your answer, just I use "put" instead of "output":

data abc;
	set sashelp.class;
		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
		do i= 1 to dim(del_name);
		if findw(Name,del_name[i],'','eir')then put;
		Comment I used "put" here instead of "output";
   end;
run;

And I was stuck because it results in all observation

Then I went for a search if there is any difference between "put" and "output", results in this document. In which, they said:

Both the PUT and OUTPUT statements create output in a DATA step. The PUT statement uses an output buffer and writes output lines to a file, the SAS log, or your display. The OUTPUT statement uses the program data vector and writes observations to a SAS data set.

There seems to be no clear difference between PUT and OUTPUT in my case being explained thoroughly here.

Could you please give me a hint about that?

Warmest regards.

Re: Issue when using FINDW to KEEP observations

Phil_NZ — Wed, 03 Mar 2021 04:29:07 GMT

@PGStats

Yay, I agree with you, even in this special case, because the style of name is similar to what is in the array so just only need

data abc;
	set sashelp.class;
		array del_name [3] $10 _temporary_ ('John','Philip','Robert');
		do i= 1 to dim(del_name);
		if name in del_name;
   end;
run;

Warm regards.

Re: Issue when using FINDW to KEEP observations

PGStats — Wed, 03 Mar 2021 17:32:46 GMT

You don't need the loop

data abc;
set sashelp.class;
array del_name [3] $10 _temporary_ ('John','Philip','Robert');
if propcase(name) in del_name;
run;