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.
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.
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.
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;
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.
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.
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.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.