Outputting counts using proc sql

[scolitti1]

Hi all,

I am trying to create different tables that show the counts of hospital admissions, ICU admissions, and intubations each week from a dataset. This code has worked before but stopped a few days ago. The tables from the output show the exact same counts for hospital admit, ICU admit, and intubations--which is not the case. 

Below is the code used to output the tables:

proc sql;
create table _0_4_Admit as
select
yr_week,
count(*) as AdmitDate
from hosprpt.hosp_cln_hs_ex
where AgeGroup='0-4 years'
group by yr_week
;
quit;

proc sql;
create table _0_4_ICU as
select
yr_week,
count(*) as ICU_AdmitDate
from hosprpt.hosp_cln_hs_ex
where AgeGroup='0-4 years'
group by yr_week
;
quit;

proc sql;
create table _0_4_Vent as
select
yr_week,
count(*) as IntubationDate
from hosprpt.hosp_cln_hs_ex
where AgeGroup='0-4 years'
group by yr_week
;
quit;

[sbxkoenk]

Hello,

Of course it's giving the same output.

The query is 3 times exactly the same.

The only difference is the output table name and the variable name you specify for count(*).

I think you have to modify your query into the input dataset.

Good luck,

Koen

[ballardw]

Count(*) will count records with the code you are showing. If none of the variables you want to count have missing values then of course the counts are the same.

If the variable, such as possibly Intubation (you did not provide the names of any variable in your source data set) might have missing values and you want to count non-missing values then  you might use

Count(Intubation ) as IntubationDate

Maybe, you data description is pretty weak.

If that is what you want then you can count all the variables in one pass through the data.

It might be a good idea to provide a data step with example data just so we know what variable names and the types you are working with. Or at least post the result of Proc Contents on your data set hosprpt.hosp_cln_hs_ex.

Any particular reason you are creating multiple data sets? Multiple data sets like this are often harder to work with.

Here is a brief example of counting records (count(*)) vs counting values of variables.

data example;
   input x y z q;
datalines;
0 1 2  .
1 2 .  .
2 . .  .
. . .  .
;

proc sql;
   create table counts as
   select count(*) as countall, count(x) as countx,
          count(y) as county, count(z) as countz,
          count(q) as countq
   from example;
quit;

[HB]

Give us an input data set.

[Reeza]

---

@scolitti1 wrote:

Hi all,

I am trying to create different tables that show the counts of hospital admissions, ICU admissions, and intubations each week from a dataset. This code has worked before but stopped a few days ago. The tables from the output show the exact same counts for hospital admit, ICU admit, and intubations--which is not the case. 

---

If it worked previously then something changed the code, this code as shown would never have worked. If you have windows, check your previous versions to revert to an older version or check with your IT team to do a roll back if you need to. 

If you want to just fix it, I suspect you either need to change the table names or you need to change the WHERE clause. 

Spoiler

---

@scolitti1 wrote:

Hi all,

I am trying to create different tables that show the counts of hospital admissions, ICU admissions, and intubations each week from a dataset. This code has worked before but stopped a few days ago. The tables from the output show the exact same counts for hospital admit, ICU admit, and intubations--which is not the case. 

Below is the code used to output the tables:

proc sql;
create table _0_4_Admit as
select
yr_week,
count(*) as AdmitDate
from hosprpt.hosp_cln_hs_ex
where AgeGroup='0-4 years'
group by yr_week
;
quit;

proc sql;
create table _0_4_ICU as
select
yr_week,
count(*) as ICU_AdmitDate
from hosprpt.hosp_cln_hs_ex
where AgeGroup='0-4 years'
group by yr_week
;
quit;

proc sql;
create table _0_4_Vent as
select
yr_week,
count(*) as IntubationDate
from hosprpt.hosp_cln_hs_ex
where AgeGroup='0-4 years'
group by yr_week
;
quit;

---

[SASKiwi]

Did you check the SAS log of the last run you did and does it report any errors or not?

If your program is not giving you the expected results then the SAS log is the first thing you should check.