DATA Step, Macro, Functions and more

Create new SAS table with count number of observations per subject

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Create new SAS table with count number of observations per subject

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!


Accepted Solutions
Solution
a month ago
Contributor
Posts: 42

Re: Create new SAS table with count number of observations per subject

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


All Replies
Solution
a month ago
Contributor
Posts: 42

Re: Create new SAS table with count number of observations per subject

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;

 

 

 

Occasional Contributor
Posts: 6

Re: Create new SAS table with count number of observations per subject

Posted in reply to KentaMURANAKA

Thank you KentaMURANKA. This works great!

PROC Star
Posts: 1,769

Re: Create new SAS table with count number of observations per subject

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;
Occasional Contributor
Posts: 6

Re: Create new SAS table with count number of observations per subject

Posted in reply to novinosrin
Thank you for your help. Appreciate it. This code counts the obs per ID but reports it as individual tables per ID.
Respected Advisor
Posts: 2,982

Re: Create new SAS table with count number of observations per subject

proc report data=have;
    columns id destination;
    define id/group;
    define destination/across;
run;
--
Paige Miller
Occasional Contributor
Posts: 6

Re: Create new SAS table with count number of observations per subject

Posted in reply to PaigeMiller
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.
Contributor
Posts: 42

Re: Create new SAS table with count number of observations per subject

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;

 

Occasional Contributor
Posts: 6

Re: Create new SAS table with count number of observations per subject

Posted in reply to KentaMURANAKA

Thank you!

Respected Advisor
Posts: 2,982

Re: Create new SAS table with count number of observations per subject

[ Edited ]

@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
Regular Learner
Posts: 1

Re: Create new SAS table with count number of observations per subject

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;
Occasional Contributor
Posts: 6

Re: Create new SAS table with count number of observations per subject

Posted in reply to PraneethSrinivas
Thank you PraneethSrinivas. This works perfectly! Appreciate all your help.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 186 views
  • 3 likes
  • 5 in conversation