Hi Everyone,
Could you please help with below:
If the variable Sub1 from dataset "Test" exists on dataset "List", I want to know its frequency(from dataset List).
The variable sub1 and sub2 are char type(default.)
Data Test;
input Sub1 $ value $;
cards;
A 100
B 102
C 102
D 90
E 33
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
Thank you 🙂
Hello @new_sas_user_4
If you are using SAS 9.4
Data Test;
input Sub1 $ value $;
cards;
100 A
102 B
103 C
90 D
33 E
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
data want;
if _n_=1 then do;
if 0 then set list;
dcl hash H (dataset:'list',multidata:'y') ;
h.definekey ("sub2") ;
h.definedone () ;
end;
set test;
do count=0 by 1 while(h.do_over(key:Sub1) eq 0);
end;
keep sub1 count;
run;
I think you have short-cut your problem description.
You should show what the desired output is for the provided example input.
From your description "sub1" has values of A B C. And none of those values, much less a variable by that name or the name SUB1 exist in "LIST"
I might be able to guess what you intended but guesses are a poor way to program computers.
Sorry about that!
Data Test;
input Sub1 $ value $;
cards;
100 A
102 B
103 C
90 D
33 E
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
Desired Output (Get the frequency of Sub1 if it exists in Dataset List):
Sub1 Count
100 1
102 1
103 0
90 3
33 0
(untested, I'm not in front of my SAS at the moment)
proc sql;
create table want as
select
a.sub1,
count(b.sub2) as count
from test a left join list b
on a.sub1 = b.sub2;
quit;
This is what I get when I run the above code:
100 3
102 3
102 3
33 3
90 3
I want this:
100 1
102 1
103 0
90 3
33 0
Hello @new_sas_user_4
If you are using SAS 9.4
Data Test;
input Sub1 $ value $;
cards;
100 A
102 B
103 C
90 D
33 E
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
data want;
if _n_=1 then do;
if 0 then set list;
dcl hash H (dataset:'list',multidata:'y') ;
h.definekey ("sub2") ;
h.definedone () ;
end;
set test;
do count=0 by 1 while(h.do_over(key:Sub1) eq 0);
end;
keep sub1 count;
run;
@new_sas_user_4 Alternatively, if you prefer boring SQL
Data Test;
input Sub1 $ value $;
cards;
100 A
102 B
103 C
90 D
33 E
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
proc sql;
create table want as
select sub1,sum(sub1=sub2) as count
from test a left join list b
on a.sub1=b.sub2
group by sub1;
quit;
Data Test;
input Sub1 $ value $;
cards;
100 A
102 B
103 C
90 D
33 E
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
proc sort data=test out=_test;
by sub1;
run;
proc sort data=list(rename=(sub2=sub1)) out=_list;
by sub1;
run;
data want;
do until(last.sub1);
merge _test(in=a) _list(in=b);
by sub1 ;
count=sum(count,a=b);
end;
if a then output;
run;
I want the freq count of the subs which exist in test. I want the freq count from List.
using the solutions you gave me @novinosrin , I get the below result:
100 1
102 1
103 0
33 0
90 0
I want :
100 1
102 1
103 0
90 3
33 0
Hello @new_sas_user_4 I just reran and tested the results , it seems to match your expected. I am not sure what you are testing against
Data Test;
input Sub1 $ value $;
cards;
100 A
102 B
103 C
90 D
33 E
;
run;
Data List;
Input Sub2 $;
cards;
100
90
90
90
102
;
Run;
data want;
if _n_=1 then do;
if 0 then set list;
dcl hash H (dataset:'list',multidata:'y') ;
h.definekey ("sub2") ;
h.definedone () ;
end;
set test;
do count=0 by 1 while(h.do_over(key: Sub1) eq 0);
end;
keep sub1 count;
run;
Results:
100 1
102 1
103 0
90 3
33 0
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!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: