I am continuing from an earlier question.
My data input set looks like the following:
ID County Race_Ethnicity NAICS_Sector Sector_Type Date
1056200 Yamhill Goods Construction 10Jun2020
1054324 Baker Goods Agriculture Forestry 10Jun2020
2314536 Grant Goods Construction 10Jun2020 .
.
2145735 Curry Goods Construction 22Jun2020
.
.
176834 Columbia Goods Manufacturing 13Nov2020
170034 Columbia Goods Manufacturing 13Nov2020
198784 Columbia Goods Manufacturing 13Nov2020
and so on. The main characteristic is that Contact ID can appear any number of times for any particular date - it is dependent on the success of the contact tracing.
I want to keep the fields shown but for the next sql code I need to have the total IDs
for each date totaled. Again the number of contacts for any date depends on the contact tracing effort and success of those efforts.
I used the following code.
Proc sql noprint;
CREATE Table SAS_EMPL.POE_Goods_Trend AS
SELECT Contact_Person_ID,
County_1,
Race_Ethnicity,
NAICS_Sector,
Sector_Type,
datepart(Created_On) AS Date label = "Date" format=date9.,
count(distinct Contact_Person_ID) AS N
FROM SAS_EMPL.POE_GOODS_FINI
Where '01JAN20'd <= Calculated Date <= '31MAR22'd
And NAICS_Sector = "GOODS"
Group by date;
*Order by 1;
quit;
However it didn't give what I need
This what I got:
Date N
04JUN2020 1
05JUN2020 1
09JUN2020 1
09JUN2020 1
09JUN2020 1
11JUN2020 1
11JUN2020 1
11JUN2020 1
11JUN2020 1
11JUN2020 1
11JUN2020 1
This what I need/want:
Date N
04JUN2020 1
05JUN2020 1
09JUN2020 3
11JUN2020 6
So I need the output to look like the last little snippet for all the dates that are in the
dataset (the cumulative total for each date).
Thank you.
For information the log accompanying the above code is
Proc sql noprint;
328 CREATE Table SAS_EMPL.POE_Goods_Trend AS
329 SELECT Contact_Person_ID,
330 County_1,
331 Race_Ethnicity,
332 NAICS_Sector,
333 Sector_Type,
334 datepart(Created_On) AS Date label = "Date" format=date9.,
335 count(distinct Contact_Person_ID) AS N
336 FROM SAS_EMPL.POE_GOODS_FINI
337 Where '01JAN20'd <= Calculated Date <= '31MAR22'd
338 And NAICS_Sector = "GOODS"
339 Group by date;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compressing data set SAS_EMPL.POE_GOODS_TREND decreased size by 60.00 percent.
Compressed is 4 pages; un-compressed would require 10 pages.
NOTE: Table SAS_EMPL.POE_GOODS_TREND created, with 2467 rows and 7 columns.
340 *Order by 1;
341 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
Thank you for your help in answering this specific request.
wlierman
... View more
