I have the following dataset. I would like to create a new dataset that totals per column and MIN and Max for the date columns
Have:
Name | Screened | Eligible | Enrolled | Rate | FirstDate | LastDate |
Amy | 9 | 3 | 3 | 33% | 12/2/2019 | 1/2/2020 |
Bianca | 12 | 5 | 4 | 33% | 12/1/2019 | 2/20/2020 |
Casey | 9 | 4 | 4 | 44% | 8/12/2019 | 1/29/2020 |
Deborah | 10 | 9 | 8 | 80% | 9/21/2019 | 3/1/2020 |
Eileen | 1 | 1 | 1 | 100% | 8/21/2019 | 8/21/2019 |
Fiona | 12 | 11 | 2 | 17% | 11/18/2019 | 2/9/2020 |
Georgia | 19 | 12 | 11 | 58% | 9/18/2019 | 12/31/2019 |
Harriet | 32 | 31 | 23 | 72% | 12/31/2019 | 2/20/2020 |
India | 12 | 10 | 7 | 58% | 4/30/2019 | 2/15/2020 |
Jennifer | 11 | 8 | 5 | 45% | 7/23/2019 | 3/1/2020 |
Want (rate = enrolled/screened)
Name | Screened | Eligible | Enrolled | Rate | FirstDate | LastDate |
Amy | 9 | 3 | 3 | 33% | 12/2/2019 | 1/2/2020 |
Bianca | 12 | 5 | 4 | 33% | 12/1/2019 | 2/20/2020 |
Casey | 9 | 4 | 4 | 44% | 8/12/2019 | 1/29/2020 |
Deborah | 10 | 9 | 8 | 80% | 9/21/2019 | 3/1/2020 |
Eileen | 1 | 1 | 1 | 100% | 8/21/2019 | 8/21/2019 |
Fiona | 12 | 11 | 2 | 17% | 11/18/2019 | 2/9/2020 |
Georgia | 19 | 12 | 11 | 58% | 9/18/2019 | 12/31/2019 |
Harriet | 32 | 31 | 23 | 72% | 12/31/2019 | 2/20/2020 |
India | 12 | 10 | 7 | 58% | 4/30/2019 | 2/15/2020 |
Jennifer | 11 | 8 | 5 | 45% | 7/23/2019 | 3/1/2020 |
Total | 127 | 94 | 68 | 54% | 4/30/2019 | 3/1/2020 |
I tried proc means and sql commands but i am really stuck
Hi @monday89
Here is one approach to achieve this, using PROC SQL:
data have;
infile datalines dlm="09"x;
input Name:$20. Screened Eligible Enrolled Rate:percent8.0 FirstDate:MMDDYY10. LastDate:MMDDYY10.;
format FirstDate LastDate MMDDYY10.;
datalines;
Amy 9 3 3 33% 12/2/2019 1/2/2020
Bianca 12 5 4 33% 12/1/2019 2/20/2020
Casey 9 4 4 44% 8/12/2019 1/29/2020
Deborah 10 9 8 80% 9/21/2019 3/1/2020
Eileen 1 1 1 100% 8/21/2019 8/21/2019
Fiona 12 11 2 17% 11/18/2019 2/9/2020
Georgia 19 12 11 58% 9/18/2019 12/31/2019
Harriet 32 31 23 72% 12/31/2019 2/20/2020
India 12 10 7 58% 4/30/2019 2/15/2020
Jennifer 11 8 5 45% 7/23/2019 3/1/2020
;
run;
proc sql;
create table want1 as
(select* from have
union corr all
select "Total" as Name,
sum(Screened) as Screened,
sum(Eligible) as Eligible,
sum(Enrolled) as Enrolled,
calculated enrolled/ calculated screened as rate,
min(FirstDate) as FirstDate,
max(LastDate) as LastDate
from have);
quit;
data want;
set want1;
format rate percent8.0;
run;
My best,
Is this for reporting purposes or do you want a SAS data set?
I need an actual dataset. Thanks! I really appreciate it
Hi @monday89
Here is one approach to achieve this, using PROC SQL:
data have;
infile datalines dlm="09"x;
input Name:$20. Screened Eligible Enrolled Rate:percent8.0 FirstDate:MMDDYY10. LastDate:MMDDYY10.;
format FirstDate LastDate MMDDYY10.;
datalines;
Amy 9 3 3 33% 12/2/2019 1/2/2020
Bianca 12 5 4 33% 12/1/2019 2/20/2020
Casey 9 4 4 44% 8/12/2019 1/29/2020
Deborah 10 9 8 80% 9/21/2019 3/1/2020
Eileen 1 1 1 100% 8/21/2019 8/21/2019
Fiona 12 11 2 17% 11/18/2019 2/9/2020
Georgia 19 12 11 58% 9/18/2019 12/31/2019
Harriet 32 31 23 72% 12/31/2019 2/20/2020
India 12 10 7 58% 4/30/2019 2/15/2020
Jennifer 11 8 5 45% 7/23/2019 3/1/2020
;
run;
proc sql;
create table want1 as
(select* from have
union corr all
select "Total" as Name,
sum(Screened) as Screened,
sum(Eligible) as Eligible,
sum(Enrolled) as Enrolled,
calculated enrolled/ calculated screened as rate,
min(FirstDate) as FirstDate,
max(LastDate) as LastDate
from have);
quit;
data want;
set want1;
format rate percent8.0;
run;
My best,
Thanks so much ! It works
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.