Condensing counts by variable using proc freq

[scolitti1]

Hi,

I am trying to create counts for the following variables: AdmitDate, ICU_AdmitDate, and IntubationDate--which are all in date form. I also have a variable called yr_week that starts on Sunday and ends on the following Saturday, and want to organize the previously stated variables by this variable. There are multiple observations per week and when I do a proc freq it counts out each separate day in the week. I have used proc sql before, but it stopped working after a couple of months (no changes in the code were made, and there were no issues seen in the log). The dataset I pull from has hundreds of thousands of observations and gets updated frequently, so it is constantly growing. I also want to separate by different age groups (0-4 years, 5-11 years, and 12-17 years) and variable counts (AdmitDate, ICU_AdmitDate, IntubationDate). 

Here is the code used to output the counts for age group '0-4 years' and variable 'AdmitDate':

data a;
set joined_raw;
if AgeGroup ne '0-4 years' then delete;
run;

proc sort data=a;
by yr_week;
run;

proc freq data=a (keep=AdmitDate yr_week count) noprint;
table AdmitDate / nopercent nocum out=_0_4_Admit;
by yr_week;
run;

proc transpose data = _0_4_Admit out = _0_4_Admit_t;
by Yr_week;
var count;
run;

Below is part of the excel export. This one in particular is for age group 0-4 years and the counts are for AdmitDate. I would like to condense the COL1-COL4. For example 2020wk10 would have one column as "2". 

Yr_week	_NAME_	_LABEL_	COL1	COL2	COL3	COL4
2020wk03	COUNT	Frequency Count	1
2020wk10	COUNT	Frequency Count	1	1
2020wk13	COUNT	Frequency Count	1	1
2020wk14	COUNT	Frequency Count	1	1	1	1
2020wk15	COUNT	Frequency Count	1	1
2020wk16	COUNT	Frequency Count	2	1
2020wk17	COUNT	Frequency Count	1	2
2020wk18	COUNT	Frequency Count	3	2	1
2020wk19	COUNT	Frequency Count	1	1	2
2020wk20	COUNT	Frequency Count	1	1	2	2

[Reeza]

Instead of BY consider doing this.

proc freq data=a (keep=AdmitDate yr_week count) noprint;
table yr_week*AdmitDate / nopercent nocum out=_0_4_Admit;
run;

or even the following and then parsing the output separately would likely be easier:

proc freq data=a (keep=AdmitDate yr_week count) noprint;
table yr_week*AgeGroup*AdmitDate / nopercent nocum out=_0_4_Admit;
run;

[ballardw]

A complete example data set with some values for all the variables of interest and what you want to see based on that example data is a good idea.

I really can't tell what you want from your description. Especially when you start adding stuff like "I also want to separate by different age groups (0-4 years, 5-11 years, and 12-17 years) ".

I suspect that a reporting procedure like Proc Tabulate might work, especially if only counts in cells are wanted.