## how to simply my steps

Solved
Frequent Contributor
Posts: 79

# how to simply my steps

``````data eq;
input ID y dm z w;
cards;
1 1 . 40 8
1 0 . 29 37
1 1 30 . 25
1 1 38 38 23
2 1 30 45 19
2 0 30 20 .
2 1 67 . .
2 1 . 27 .
3 0 33 23 46
3 1 . 12 56
3 0 . . 34
3 1 . 45 .
4 1 56 45 23
4 0 67 13 67
4 0 . 35 13
4 1 48 35 56
;
run;

data eq1;
input ID y dm z w;
cards;
1 1 30 . 25
3 1 . 45 .
4 1 48 25 56
;
run;

data c;
set eq;
if dm=. then missindex=0;
else missindex=1;
if first.id then sumindex = missindex;
else sumindex+missindex;
by id;
run;

data d;
set c;
by id;
if first.id and missindex=0 then output;
if last.id and sumindex=1 then output;
run;

proc sql;
create table want as
select * from eq where id not in (select distinct id  from d) ;
quit;

proc sql;
create table eq4 as
select * from want where id in (select distinct id  from eq1)
quit;

proc sql;
create table eq5 as
select * from eq1 where id in (select distinct id  from eq4)
quit;

data eq6;
set eq5 eq4;
run;``````

What I want to do is delete the ID with first DM missing and delete the ID with all the rest DM missing except first DM in eq. Then, select the common ID to merge with eq1. So the result will be only ID 4 with 5 observations.

My code works. But it seems redundant. Could anyone help me to simplify it into one or two steps by sql and data step? Thanks

Accepted Solutions
Solution
‎03-13-2018 05:59 PM
Super User
Posts: 2,061

## Re: how to simply my steps

Posted in reply to xiangpang
``````data eq;
input ID y dm z w;
cards;
1 1 . 40 8
1 0 . 29 37
1 1 30 . 25
1 1 38 38 23
2 1 30 45 19
2 0 30 20 .
2 1 67 . .
2 1 . 27 .
3 0 33 23 46
3 1 . 12 56
3 0 . . 34
3 1 . 45 .
4 1 56 45 23
4 0 67 13 67
4 0 . 35 13
4 1 48 35 56
;
run;

data eq1;
input ID y dm z w;
cards;
1 1 30 . 25
3 1 . 45 .
4 1 48 25 56
;
run;

data want;
if _N_ = 1 then do;
declare hash h(dataset:'eq1');
h.defineKey('id');
h.defineData(all:'y');
h.defineDone();
end;
_flag=0;__flag=0;
do until(last.id);
set eq;
by id;
if first.id and missing(dm) then _flag=1;
else if first.id and not missing(dm) then __flag=1;
else if  __flag and  missing(dm) then __flag=1;else __flag=0;
end;
do until(last.id);
set eq end=l;
by id;
if not(_flag or __flag) then do;
rc=h.check();
if rc=0 then output;
if rc=0 and last.id then do; h.find();output;end;
end;
end;
drop rc _:;
run;``````

All Replies
Super User
Posts: 13,941

## Re: how to simply my steps

Posted in reply to xiangpang

I don't know if this much improvement though it involves fewer intermediate data sets. The order of the result is also different if that matters.

```data work.c;
set eq;
by id;
if first.id and dm ne .;
run;
data work.d;
set eq1;
by id;
if first.id and dm ne .;
run;

proc sql;
create table want as
select b.*
from (select Id from (select id from work.c intersect
select id from work.d )
)
as a
left join
( select * from eq union all
select * from eq1) as b
on a.id = b.id;
quit;```
Frequent Contributor
Posts: 79

## Re: how to simply my steps

Thanks for your reply. I think the sql part is better than mine. But it still has room for more simplification.

Thanks again.

Super User
Posts: 13,941

## Re: how to simply my steps

Posted in reply to xiangpang

@xiangpang wrote:

Thanks for your reply. I think the sql part is better than mine. But it still has room for more simplification.

Thanks again.

Perhaps but when you have a requirement for order of appearance (first of ID in two separate data sources and the value of a second variable) I suspect you will find it takes awhile to get much simpler code.

Frequent Contributor
Posts: 79

## Re: how to simply my steps

Yeah, it is possible. I am just a beginner I am happy to learn and thanks for your help.

Solution
‎03-13-2018 05:59 PM
Super User
Posts: 2,061

## Re: how to simply my steps

Posted in reply to xiangpang
``````data eq;
input ID y dm z w;
cards;
1 1 . 40 8
1 0 . 29 37
1 1 30 . 25
1 1 38 38 23
2 1 30 45 19
2 0 30 20 .
2 1 67 . .
2 1 . 27 .
3 0 33 23 46
3 1 . 12 56
3 0 . . 34
3 1 . 45 .
4 1 56 45 23
4 0 67 13 67
4 0 . 35 13
4 1 48 35 56
;
run;

data eq1;
input ID y dm z w;
cards;
1 1 30 . 25
3 1 . 45 .
4 1 48 25 56
;
run;

data want;
if _N_ = 1 then do;
declare hash h(dataset:'eq1');
h.defineKey('id');
h.defineData(all:'y');
h.defineDone();
end;
_flag=0;__flag=0;
do until(last.id);
set eq;
by id;
if first.id and missing(dm) then _flag=1;
else if first.id and not missing(dm) then __flag=1;
else if  __flag and  missing(dm) then __flag=1;else __flag=0;
end;
do until(last.id);
set eq end=l;
by id;
if not(_flag or __flag) then do;
rc=h.check();
if rc=0 then output;
if rc=0 and last.id then do; h.find();output;end;
end;
end;
drop rc _:;
run;``````
Frequent Contributor
Posts: 79

## Re: how to simply my steps

Posted in reply to novinosrin

Thanks for your reply. I have to admit I did not write the codes in this way before. Could you provide a link about the use of hash and h. ()? I would like to learn about it.

Super User
Posts: 2,061

## Re: how to simply my steps

Posted in reply to xiangpang

@xiangpang There is not one link. Google, ask.com,  tutorials, sugi articles, books,, learning from here, University publishing like ucla, cambrdige, NC state and many more and counting.   Have fun!

Frequent Contributor
Posts: 79

## Re: how to simply my steps

Posted in reply to novinosrin

Thanks

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 8 replies
• 171 views
• 2 likes
• 3 in conversation