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!
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;
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;
Thank you KentaMURANKA. This works great!
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;
proc report data=have;
columns id destination;
define id/group;
define destination/across;
run;
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;
Thank you!
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.