BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Simba
Calcite | Level 5

Hi, I have dataset below 

 

IDDestination
aa1
aa1
aa1
aa2
aa1
bb1
bb3
bb3
bb4
bb4
cc1
cc2
cc3
cc1
cc1
cc1
dd4
dd4
dd4
dd1

 

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

 

IDDest_Count1Dest_Count2Dest_Count3Dest_Count4
aa41  
bb1 22
cc411 
dd1  3

 

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

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
KentaMURANAKA
Pyrite | Level 9

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;

 

 

 

View solution in original post

11 REPLIES 11
KentaMURANAKA
Pyrite | Level 9

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
Calcite | Level 5

Thank you KentaMURANKA. This works great!

novinosrin
Tourmaline | Level 20
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
Calcite | Level 5
Thank you for your help. Appreciate it. This code counts the obs per ID but reports it as individual tables per ID.
PaigeMiller
Diamond | Level 26
proc report data=have;
    columns id destination;
    define id/group;
    define destination/across;
run;
--
Paige Miller
Simba
Calcite | Level 5
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
Pyrite | Level 9

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;

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
PraneethSrinivas
Calcite | Level 5

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
Calcite | Level 5
Thank you PraneethSrinivas. This works perfectly! Appreciate all your help.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 7818 views
  • 4 likes
  • 5 in conversation