topic Re: Count is giving me one extra row in SAS Programming

Count is giving me one extra row

VarunD — Fri, 14 Sep 2018 13:53:10 GMT

I am reading a SAS data set in PROC SQL and using COUNT. It is giving me one extra count - I suspect it is counting the header as well. How do I fix this ?

Here's the code:

PROC SQL;
CREATE TABLE Nat_1 AS
SELECT DISTINCT Loc_Nat,
COUNT(MBR_ID) AS Other
FROM Employee4
WHERE Loc_Nat = 'National'
QUIT;

Re: Count is giving me one extra row

RW9 — Fri, 14 Sep 2018 13:57:08 GMT

And how will we tell from that? Provide test data in the form of a datastep!

The sql looks wrong to me, you are using distinct, and counting with a where clause. Seems to be counter intuitive.

proc sql;
  create table nat_1 as
  select loc_nat,
         count(mbr_id) as other
  from   employee4
  group by loc_nt
  having loc_nat="National";
quit;

Re: Count is giving me one extra row

novinosrin — Fri, 14 Sep 2018 14:27:03 GMT

Please post a sample of what you 'HAVE' and what your desired output 'WANT'

Re: Count is giving me one extra row

ballardw — Fri, 14 Sep 2018 14:44:19 GMT

@VarunD wrote:

I am reading a SAS data set in PROC SQL and using COUNT. It is giving me one extra count - I suspect it is counting the header as well. How do I fix this ?

Here's the code:

PROC SQL;
CREATE TABLE Nat_1 AS
SELECT DISTINCT Loc_Nat,
COUNT(MBR_ID) AS Other
FROM Employee4
WHERE Loc_Nat = 'National'
QUIT;

The "header" would only get counted if you imported the header as a value for the variables.

Re: Count is giving me one extra row

VarunD — Fri, 14 Sep 2018 14:57:05 GMT

I am sorry, I found the issue with the data. Thanks for your response!

Re: Count is giving me one extra row

VarunD — Fri, 14 Sep 2018 14:57:25 GMT

Sorry the issue was with data. I found it. Thanks!