how to remove the entire records of each id if it has values as either missing or zero.
i.e if i have id and have 5 values to it and next id 4 values and one of them is missing or zero than i want to ommit the entire id.
Any help
Assuming that you mean that you have data like this:
data have;
input id value;
Cards;
1 3
1 4
1 3
2 0
2 3
3 .
3 5
3 6
4 1
;run;
And you want to get rid of all the ID=2 records, because there is one with VALUE=0, and all the ID=3 records because there is one with a missing VALUE.
Then this should do the trick, provided your data is sorted by ID:
data want;
merge have(where=(not value) in=null) have;
by id;
if not null;
run;
Hi ,
data ds;
infile datalines;
input id val;
datalines;
1 1
2 0
3 5
4 0
5 2
;
run;
data ds1;
set ds;
array dat[*] _all_;
do i=1 to dim(dat);
if dat(i)=0 then delete;
end;
output;
run;
Thanks
data ds; infile datalines; input id val; datalines; 1 1 1 0 2 0 2 1 2 2 2 0 3 5 4 0 5 2 ; run;
In this case id 2 should not come as it has one value val as 0. I dont want any id if has either 0 or missing and want the rest of the id and val
Hi,
Then try,
data ds2;
merge ds(where=(not val) in=null) ds;
by id;
if not null;
run;
Thanks
It would help if you provided test data in the form of a datastep, and what the output should look like. Just a guess:
data have (drop=flag); set have; array n{*} _numeric_; array c{*} _character_; do over n; if n in (.,0) then flag=1; end; do over c; if c="" then flag=1; end; if flag ne 1; run;
Not tested as nothing provided.
Assuming that you mean that you have data like this:
data have;
input id value;
Cards;
1 3
1 4
1 3
2 0
2 3
3 .
3 5
3 6
4 1
;run;
And you want to get rid of all the ID=2 records, because there is one with VALUE=0, and all the ID=3 records because there is one with a missing VALUE.
Then this should do the trick, provided your data is sorted by ID:
data want;
merge have(where=(not value) in=null) have;
by id;
if not null;
run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.