Creating a table of counts from unique ID

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Creating a table of counts from unique ID

Hello,

I am a beginner at SAS and am trying to figure out how to create a somewhat complex table with unique ID counts. A similar, simplified example of my data set is below:

ObsIDHas FileAvailableLocationCollection_Site
1100111LeftInternal
2100110LeftInternal
3100211RightExternal
4100311RightExternal
5100301RightExternal

I want unique counts by ID. I would like the table to look somewhat like this:

LocationCollection_Site# Has_File# Available
LeftInternal11
External00
RightInternal00
External22

As you'll see, even though there are 2 observations for Left-Internal-Has_File, they are only from 1 ID. Same with Right-External-Available (2 IDs from 3 obs). I have tried using PROC TABULATE to get the table above however I haven't been able to figure out how to get unique counts.

Perhaps it may be easier to group by ID and merge observations, replacing the 0's with 1 where appropriate, and then de-duplicate. For example, in observation 2 replacing the 0 for "Available" since there is a 1 for the same ID in observation 1. However I'm not sure how to do that either.

Any help is much appreciated!


Accepted Solutions
Solution
‎02-10-2015 09:57 AM
Super Contributor
Posts: 308

Re: Creating a table of counts from unique ID

Hello,

Another solution

data have;
input ID Has_File Available Location :$5. Collection_Site $ ;
cards;
1001 1 1 Left Internal
1001 1 0 Left Internal
1001 1 0 Left External
1001 0 0 Left External
1001 1 0 Right Internal
1001 0 0 Right Internal
1001 1 0 Right External
1001 1 0 Right External
1002 1 0 Left External
1002 1 1 Right External
1003 1 1 Right External
1003 0 1 Right External
;

proc sort data=have;
by id Location Collection_Site descending Has_File ;
run;

data have;
set have;
by id Location Collection_Site ;

if first.Collection_Site then do;var_has_file=has_file;end;
else var_has_file=0;


run;

proc sort data=have;
by id Location Collection_Site descending Available ;
run;

data have;
set have;
by id Location Collection_Site ;

if first.Collection_Site then do;var_Available=Available;end;
else var_Available=0;

run;

data temp;
Location='Right';Collection_Site='Internal';output;
Location='Left';Collection_Site='Internal';output;
Location='Right';Collection_Site='External';output;
Location='Left';Collection_Site='External';output;
run;

proc means data=have classdata=temp nway noprint;
class Location Collection_Site;
var var_has_file var_Available;
output out=want(drop=_type_ _freq_) sum=;
run;

View solution in original post


All Replies
Super User
Posts: 11,134

Re: Creating a table of counts from unique ID

You may be able to do something like if the data of interest are basically 0/1/missing :

proc summary data=have nway;

class location collection_site id;

var has_file available;

output out=temp max=;

run;

proc tabulate data=temp;

Class location collection_site;

var has_file available;

table location*collection_site,

    ( has_file available) *sum=''

;

run;

Super User
Posts: 5,388

Re: Creating a table of counts from unique ID

Not sure if you can solve this in PROC TABULATE.

Perhaps you could use SQL to do the aggregation, and let TABULATE just do the lay-out.

Hint: count(distinct ID)

Data never sleeps
Super User
Posts: 9,874

Re: Creating a table of counts from unique ID

data have;
input obs id HasFile Available  Location $ site $;
cards;
1     1001     1     1     Left     Internal
2     1001     1     0     Left     Internal
3     1002     1     1     Right     External
4     1003     1     1     Right     External
5     1003     0     1     Right     External
;
run;
proc sql;
 create table want as
 select a.*,coalesce(HasFile,0) as HasFile,  coalesce(Available,0) as Available
  from (select * from (select distinct Location from have),(select distinct site from have)) as a
   left join (select Location,site,count(distinct id) as HasFile from have where HasFile=1 group by  Location,site) as b
    on a.Location=b.Location and a.site=b.site
   
   left join (select Location,site,count(distinct id) as Available from have where Available=1 group by  Location,site) as c
    on a.Location=c.Location and a.site=c.site ;
quit;

Xia Keshan

Solution
‎02-10-2015 09:57 AM
Super Contributor
Posts: 308

Re: Creating a table of counts from unique ID

Hello,

Another solution

data have;
input ID Has_File Available Location :$5. Collection_Site $ ;
cards;
1001 1 1 Left Internal
1001 1 0 Left Internal
1001 1 0 Left External
1001 0 0 Left External
1001 1 0 Right Internal
1001 0 0 Right Internal
1001 1 0 Right External
1001 1 0 Right External
1002 1 0 Left External
1002 1 1 Right External
1003 1 1 Right External
1003 0 1 Right External
;

proc sort data=have;
by id Location Collection_Site descending Has_File ;
run;

data have;
set have;
by id Location Collection_Site ;

if first.Collection_Site then do;var_has_file=has_file;end;
else var_has_file=0;


run;

proc sort data=have;
by id Location Collection_Site descending Available ;
run;

data have;
set have;
by id Location Collection_Site ;

if first.Collection_Site then do;var_Available=Available;end;
else var_Available=0;

run;

data temp;
Location='Right';Collection_Site='Internal';output;
Location='Left';Collection_Site='Internal';output;
Location='Right';Collection_Site='External';output;
Location='Left';Collection_Site='External';output;
run;

proc means data=have classdata=temp nway noprint;
class Location Collection_Site;
var var_has_file var_Available;
output out=want(drop=_type_ _freq_) sum=;
run;

New Contributor
Posts: 2

Re: Creating a table of counts from unique ID

Thank you!! I was able to use your first. last. coding and then PROC TABULATE the table the way I wanted. Really appreciate your help!

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 270 views
  • 6 likes
  • 5 in conversation