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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.