Hi All,
I have a programing question and would appreciate some suggestions.
I have a data set which has 6 variables: A1, A2, A3, A4, rank and flag.
A1,A2,A3,A4 is by group and within that by group I want to delete flag=1 but its not that simple, I want to delete only those flags which leaves me with consecutive rank variable.
For eg:
A1 A2 A3 A4 Rank Flag
1 1 1 1 1 0
1 1 1 1 2 1 -by Group1
1 1 1 1 3 0
1 1 1 1 4 1
1 1 1 2 1 1
1 1 1 2 2 1
1 1 1 2 3 0
1 1 1 2 4 1 -> by Group2
1 1 1 2 5 1
1 1 1 2 6 0
1 1 1 2 7 1
Result I want:
A1 A2 A3 A4 Rank Flag
1 1 1 1 1 0
1 1 1 1 2 1 -> by Group1
1 1 1 1 3 0
1 1 1 2 3 0
1 1 1 2 4 1 -> by Group2
1 1 1 2 5 1
1 1 1 2 6 0
Here important thing is not to break the sequence of rank
variable. I cant have rank 3,6 only in group 2
Typically you would need more than one pass, here is an example using 2XDOW:
data have;
input A1 A2 A3 A4 Rank Flag;
cards;
1 1 1 1 1 0
1 1 1 1 2 1
1 1 1 1 3 0
1 1 1 1 4 1
1 1 1 2 1 1
1 1 1 2 2 1
1 1 1 2 3 0
1 1 1 2 4 1
1 1 1 2 5 1
1 1 1 2 6 0
1 1 1 2 7 1
;;;;
run;
data want;
do until (last.flag);
set have;
by a4 flag notsorted;
if (first.a4 or last.a4) and flag then
_d=1;
end;
do until (last.flag);
set have;
by a4 flag notsorted;
if missing(_d) then
output;
end;
run;
Regards,
Haikuo
Typically you would need more than one pass, here is an example using 2XDOW:
data have;
input A1 A2 A3 A4 Rank Flag;
cards;
1 1 1 1 1 0
1 1 1 1 2 1
1 1 1 1 3 0
1 1 1 1 4 1
1 1 1 2 1 1
1 1 1 2 2 1
1 1 1 2 3 0
1 1 1 2 4 1
1 1 1 2 5 1
1 1 1 2 6 0
1 1 1 2 7 1
;;;;
run;
data want;
do until (last.flag);
set have;
by a4 flag notsorted;
if (first.a4 or last.a4) and flag then
_d=1;
end;
do until (last.flag);
set have;
by a4 flag notsorted;
if missing(_d) then
output;
end;
run;
Regards,
Haikuo
@anna_nag,
DOW loop is an classic data step implementation that is both powerful and convenient to use. Recently the Hash() and Proc SQL have stolen some thunders from DOW, but as you can see, it still shines here. However, if you have never used or heard of it, it would be very hard for me to explain it in a format of short post. The concept of DOW touches the heart of data step. Here are some references that you may want to take a look:
http://support.sas.com/resources/papers/proceedings12/156-2012.pdf
http://www.pharmasug.org/proceedings/2012/AD/PharmaSUG-2012-AD29.pdf
Another key of my code is to use option: "notsorted". This is to take your current data structure as is, then group it using "by variables". Please also do a search to learn more.
Regards,
Haikuo
data have; input A1 A2 A3 A4 Rank Flag ; cards; 1 1 1 1 1 0 1 1 1 1 2 1 1 1 1 1 3 0 1 1 1 1 4 1 1 1 1 2 1 1 1 1 1 2 2 1 1 1 1 2 3 0 1 1 1 2 4 1 1 1 1 2 5 1 1 1 1 2 6 0 1 1 1 2 7 1 ; run; data have; set have; by a1 a2 a3 a4; if first.a4 or flag ne lag(flag) then n+1; run; proc sql; create table want as select * from have except select * from have group by a1,a2,a3,a4 having (n eq max(n) and flag=1) or (n eq min(n) and flag=1); quit;
Xia Keshan
Thanks..it works!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.