Count is giving me one extra row

[VarunD]

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;

[RW9]

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;

[VarunD]

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

[novinosrin]

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

[VarunD]

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

[ballardw]

---

@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.