Home SAS: Translate code from proc SQL, regarding counter of distinct pair of variables from a dataset

Questions

SAS: Translate code from proc SQL, regarding counter of distinct pair of variables from a dataset

March 12, 2023

I want to get the same results with SAS code, as I did with proc SQL. I have junior level knowledge on SAS code, so any assistance would be nice. Thanks in advance for your time.

my target is the following table:

id  counter
1   3
2   2
3   3

data example1;
input id x y $ k z;
cards;
1 2 A 1 60
1 6 B 2 70
1 2 C 3 100
2 2 B 2 10
2 3 C 3 67
3 2 A 1 81
3 3 B 2 63
3 5 F 6 55
;
run;

proc sql;
  select id, count(distinct put(id,best12.)||put(k,best12.)) AS counter
    from example1
    group by id;
quit;

I tried proc freq, proc sort but didn’t manage to make it work.

>Solution :

Run two Proc FREQ steps.

data have;
input id x y $ k z;
cards;
1 2 A 1 60
1 6 B 2 70
1 2 C 3 100
2 2 B 2 10
2 3 C 3 67
3 2 A 1 81
3 3 B 2 63
3 5 F 6 55
3 6 G 6 55
3 7 H 6 55
3 8 I 7 55
3 9 J 7 55
3 0 K 1 55
;
run;

proc freq noprint data=have ;
  table id*k / out=freq1;
run;
proc freq noprint data=freq1;
  table id / out=want (keep=id count);
run;