Create new SAS table with count number of observations per subject

[Simba]

Hi, I have dataset below 

ID	Destination
aa	1
aa	1
aa	1
aa	2
aa	1
bb	1
bb	3
bb	3
bb	4
bb	4
cc	1
cc	2
cc	3
cc	1
cc	1
cc	1
dd	4
dd	4
dd	4
dd	1

From this I want to create a new SAS table like the one below counting each destination per ID:

ID	Dest_Count1	Dest_Count2	Dest_Count3	Dest_Count4
aa	4	1
bb	1		2	2
cc	4	1	1
dd	1			3

I am sure there is a way to use Proc sql. I just cant get it to work 

Thanks!

[KentaMURANAKA · Accepted solution]

Hi, Simba:

If you use SQL procedure, you can use TRANSPOSE procedure after submitting the code below.

proc sql;
    create table work.stat_2 as
    select id, count(destination)
    from work.test
    group by id, destination;
quit;

I'm sorry, but I'm not familiar with SQL well, so I made Proc FREQ & TRANSPOSE Used version.

data test;
    input id$ destination @@;
    datalines;
    aa 1 aa 1 aa 1 aa 2 aa 1
    bb 1 bb 3 bb 3 bb 4 bb 4
    cc 1 cc 2 cc 3 cc 1 cc 1 cc 1
    dd 4 dd 4 dd 4 dd 1
    ;
run;
proc freq data=test noprint;
    tables destination / out=stat_1(drop=percent);
    by id;
run;
proc sort data=stat_1 nodupkey out=dummy(keep=id);
    by id;
run;
data dummy_1;
    set dummy;
    do destination=1 to 4 by 1;
        count=0;
        output;
    end;
run;
data stat_1_1;
    attrib cntc
        length=$200.;
    merge dummy_1(in=a)
          stat_1(in=b)
          ;
    by id destination;
    if a;
    if count notin (., 0) then cntc=cats(put(round(count), best.));
    else cntc="";
run;
proc transpose data=stat_1_1 prefix=dest_count out=stat_1_2;
    var cntc;
    by id;
    id destination;
run;

[Simba]

Thank you KentaMURANKA. This works great!

[novinosrin]

data have;
input ID $	Destination;
cards;
aa	1
aa	1
aa	1
aa	2
aa	1
bb	1
bb	3
bb	3
bb	4
bb	4
cc	1
cc	2
cc	3
cc	1
cc	1
cc	1
dd	4
dd	4
dd	4
dd	1
;

proc freq data=have;
by id;
tables destination/out=temp;
run;

proc transpose data=temp out=want(drop=_:) prefix=Dest_count;
by id;
var count;
id  destination ;
run;

[Simba]

Thank you for your help. Appreciate it. This code counts the obs per ID but reports it as individual tables per ID.

[PaigeMiller]

proc report data=have;
    columns id destination;
    define id/group;
    define destination/across;
run;

[Simba]

Thank you PaigeMiller. This code gives me a report table with the values correctly tabulated. The code by KentaMURANKA gives me the same response but as a SAS dataset that can be further analyzed.

[KentaMURANAKA]

Hi, Simba:

If you want SAS dataset by submitting Mr. PaigeMiller's code, you should use OUT= option in REPORT Procedure.

data have;
input ID $	Destination;
cards;
aa	1
aa	1
aa	1
aa	2
aa	1
bb	1
bb	3
bb	3
bb	4
bb	4
cc	1
cc	2
cc	3
cc	1
cc	1
cc	1
dd	4
dd	4
dd	4
dd	1
;
proc report data=have out=want;
    columns id destination;
    define id/group;
    define destination/across;
run;

[Simba]

Thank you!

[PaigeMiller]

---

@Simba wrote:
Thank you PaigeMiller. This code gives me a report table with the values correctly tabulated. The code by KentaMURANKA gives me the same response but as a SAS dataset that can be further analyzed.

---

And a simple change to my code gives you the SAS dataset that you want. I will leave that as a homework assignment for you, to look up in the documentation how to get the data set out from PROC REPORT.

By the way, your original request was not for a SAS dataset, it was for a "SAS table" (your words, not mine) and I gave you code for a table.

[PraneethSrinivas]

Here's how we can do it with PROC SQL ,

proc sql ;
	create table test1 as 
	select id, DESTINATION, count(destination) as cnt 
	from test group by 1,2 ; quit;

proc transpose data=test1 prefix=dest_count out=test2 (drop=_name_)  ;
	var cnt ;
	by id ;
	id destination ;
run;

[Simba]

Thank you PraneethSrinivas. This works perfectly! Appreciate all your help.