DATA Step, Macro, Functions and more

Creating a new table with unique counts and percentage of an old table

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

Creating a new table with unique counts and percentage of an old table

Hello, I have a dataset that contains 4 variables. "ID" is a unique identifier of a person in my dataset. "Location Type" is a type of location that the person is in, and only ranges from 1 to 3. "Screen" is if they were ever medically screened. "Status" has 3 entries, positive, negative, or blank. It is blank when the patient has not been screened. Here is the sample dataset:

 

data JANFEB.SAMPLEUNIQUE;
  infile datalines dsd truncover;
  input ID:BEST. LocationType:BEST. Screen:$3. Result:$8.;
datalines4;
1,1,Yes,Positive
2,2,No,
3,3,Yes,Negative
4,1,No,
5,2,Yes,Positive
6,3,No,
7,1,Yes,Negative
8,2,No,
9,3,Yes,Positive
10,1,No,
11,1,Yes,Negative
12,2,Yes,Positive
13,3,Yes,Negative
14,1,No,
;;;;

How do I create a table from this dataset that groups screening and status results in this following way?

 

Location TypeScreenedPositiveTotalScreening RatePositive Prevalence
130210030%7%
240320020%8%
350510050%10%

 

Here are the explanation of the columns:

- Screened: Unique count under each location type where the screened variable had 'Yes' in it.

- Positive: Unique count under each location type where the status variable had 'Positive' in it.

- Total: Unique count under each location type in the dataset.

- Screening rate: Screened/Total

- Positive prevalence; Positive/Screen

 

Thank you so much for your help!


Accepted Solutions
Solution
‎04-24-2017 05:28 AM
Super User
Super User
Posts: 7,942

Re: Creating a new table with unique counts and percentage of an old table

Your test data doesn't match your example output, or if it does, eplain how a count of 3 id's can yield 30?  Anyways this should get you started:

data sampleunique;
  infile datalines dsd truncover;
  input ID:BEST. LocationType:BEST. Screen:$3. Result:$8.;
datalines4;
1,1,Yes,Positive
2,2,No,
3,3,Yes,Negative
4,1,No,
5,2,Yes,Positive
6,3,No,
7,1,Yes,Negative
8,2,No,
9,3,Yes,Positive
10,1,No,
11,1,Yes,Negative
12,2,Yes,Positive
13,3,Yes,Negative
14,1,No,
;;;;
run;

proc sql;
  create table WANT as
  select  LOCATIONTYPE,
          sum(case when SCREEN="Yes" then 1 else 0 end) as SCREENED,
          sum(case when char(RESULT,1)="P" then 1 else 0 end) as POSITIVE,
          count(*) as TOTAL,
          (CALCULATED SCREENED / CALCULATED TOTAL) * 100 as SCREENINGRATE,
          (CALCULATED POSITIVE / CALCULATED SCREENED) * 100 as POSITIVEPREVALENCE
  from    SAMPLEUNIQUE
  group by LOCATIONTYPE;
quit;

View solution in original post


All Replies
Solution
‎04-24-2017 05:28 AM
Super User
Super User
Posts: 7,942

Re: Creating a new table with unique counts and percentage of an old table

Your test data doesn't match your example output, or if it does, eplain how a count of 3 id's can yield 30?  Anyways this should get you started:

data sampleunique;
  infile datalines dsd truncover;
  input ID:BEST. LocationType:BEST. Screen:$3. Result:$8.;
datalines4;
1,1,Yes,Positive
2,2,No,
3,3,Yes,Negative
4,1,No,
5,2,Yes,Positive
6,3,No,
7,1,Yes,Negative
8,2,No,
9,3,Yes,Positive
10,1,No,
11,1,Yes,Negative
12,2,Yes,Positive
13,3,Yes,Negative
14,1,No,
;;;;
run;

proc sql;
  create table WANT as
  select  LOCATIONTYPE,
          sum(case when SCREEN="Yes" then 1 else 0 end) as SCREENED,
          sum(case when char(RESULT,1)="P" then 1 else 0 end) as POSITIVE,
          count(*) as TOTAL,
          (CALCULATED SCREENED / CALCULATED TOTAL) * 100 as SCREENINGRATE,
          (CALCULATED POSITIVE / CALCULATED SCREENED) * 100 as POSITIVEPREVALENCE
  from    SAMPLEUNIQUE
  group by LOCATIONTYPE;
quit;
Frequent Contributor
Posts: 77

Re: Creating a new table with unique counts and percentage of an old table

You're right. The output doesn't match those numbers. I just arbitrarily put in numbers for the sake of creating a sample output of what I would like it to look like. Your code works! Thanks so much.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 148 views
  • 0 likes
  • 2 in conversation