Hi all
Today a have a problem and I don't know how resolve it! Any clue...
I have something like this:
Var1 | Var2 | Var3 | Var4 |
1 | A | 2 | |
1 | A | 16 | 20 |
1 | A | 31 | 10 |
2 | B | 6 | |
2 | B | 97 | 40 |
3 | C | 84 | |
3 | C | 64 | 20 |
4 | D | 97 | |
4 | D | 63 | |
4 | D | 12 | 30 |
4 | D | 41 | 10 |
And I want to select some groups of observations with same conditions. For example, if Var4='20', I have to select all group with the same characteristics in 'Var1' and 'Var2'. Something like this:
Var1 | Var2 | Var3 | Var4 |
1 | A | 2 | |
1 | A | 16 | 20 |
1 | A | 31 | 10 |
3 | C | 84 | |
3 | C | 64 | 20 |
I sorted Var1 and Var2 previously, but after that, I don't know how to proceed.
I will appreciate your help! Please...
I like Art's approach, but I think you need to tweak it:
data want (drop=select);
do until (last.Var2);
set have;
by Var1 Var2;
if Var4 eq 20 then select=1;
end;
do until (last.Var2);
set have;
by Var1 Var2;
if select then output;
end;
run;
Sounds like you are trying to do something like the following:
data want (drop=select); do until (last.Var1); set have; by Var1; if Var4 eq 20 then select=1; end; do until (last.Var1); set have; by Var1; if select then output; end; run;
Art, CEO, AnalystFinder.com
I like Art's approach, but I think you need to tweak it:
data want (drop=select);
do until (last.Var2);
set have;
by Var1 Var2;
if Var4 eq 20 then select=1;
end;
do until (last.Var2);
set have;
by Var1 Var2;
if select then output;
end;
run;
SQL solution.
data have;
infile cards truncover expandtabs;
input Var1 Var2 $ Var3 Var4;
cards;
1 A 2
1 A 16 20
1 A 31 10
2 B 6
2 B 97 40
3 C 84
3 C 64 20
4 D 97
4 D 63
4 D 12 30
4 D 41 10
;
run;
proc sql;
create table want as
select * from have
group by var1
having sum(var4=20) ne 0;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.