Freq count from another dataset

[new_sas_user_4]

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 🙂

[ballardw]

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.

[new_sas_user_4]

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

[Kurt_Bremser]

(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;

[new_sas_user_4]

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

[novinosrin · Accepted solution]

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;

[novinosrin]

@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;

[novinosrin]

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;

[new_sas_user_4]

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

[novinosrin]

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

[new_sas_user_4]

my bad 😞 I was using the wrong dataset. 

Your solution is perfect !

Thanks a ton @novinosrin  🙂