topic Re: Freq count from another dataset in SAS Procedures

Freq count from another dataset

new_sas_user_4 — Thu, 11 Jul 2019 16:50:04 GMT

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 🙂

Re: Freq count from another dataset

ballardw — Thu, 11 Jul 2019 17:36:52 GMT

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.

Re: Freq count from another dataset

new_sas_user_4 — Thu, 11 Jul 2019 18:51:13 GMT

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

Re: Freq count from another dataset

Kurt_Bremser — Thu, 11 Jul 2019 19:14:07 GMT

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

Re: Freq count from another dataset

new_sas_user_4 — Thu, 11 Jul 2019 19:19:21 GMT

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

Re: Freq count from another dataset

novinosrin — Thu, 11 Jul 2019 20:28:27 GMT

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;

Re: Freq count from another dataset

novinosrin — Thu, 11 Jul 2019 20:48:54 GMT

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

Re: Freq count from another dataset

novinosrin — Thu, 11 Jul 2019 23:25:19 GMT


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;

Re: Freq count from another dataset

new_sas_user_4 — Fri, 12 Jul 2019 18:46:33 GMT

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

Re: Freq count from another dataset

novinosrin — Fri, 12 Jul 2019 18:52:52 GMT

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

Re: Freq count from another dataset

new_sas_user_4 — Fri, 12 Jul 2019 20:00:26 GMT

my bad 😞 I was using the wrong dataset.

Your solution is perfect !

Thanks a ton @novinosrin 🙂