topic Re: Counting distinct inputs in a column using proc sql in SAS Programming

Counting distinct inputs in a column using proc sql

unwashedhelimix — Tue, 03 Dec 2024 07:26:43 GMT

I have a SAS data set I read in:

libname mysas "path to datafile location";

data mysas.MyData;
  set mysas.datafile; 
run;

There is a column named "SPECIES NAME" that has many different types of species. I want to get a count of how many species there are, and here is what I did:

proc sql;
  select count(distinct 'SPECIES NAME') as Number_of_Species
  from mysas.MyData;
quit;

However, it tells me there is only 1 Number_of_Species. Looking at the data set, this is definitely not accurate. How can I fix this?

Because the 2nd (and 3rd I guess) thing I want to do is for each of the species, I want to get a count on how many years have been the data recorded for each species. Conversely, I also want to create an ordered list of all the years, then for each year, give the number of species that corresponds to that year.

There is a column in MyData called "YEAR" next to "SPECIES NAME" and it looks a bit like this:

YEAR SPECIES NAME
2021 SPECIES A
2022 SPECIES A

2023 SPECIES A

2020 SPECIES B

2022 SPECIES B

I am not quite sure how I would approach that in proc sql.

Re: Counting distinct inputs in a column using proc sql

Ksharp — Tue, 03 Dec 2024 07:49:18 GMT

Using liternal name 'SPECIES NAME'n to represent this variable, otherwise sas would take 'SPECIES NAME' (without 'n') as a string ,therefore you would get ONLY ONE level.

option validvarname=any;
proc sql;
  select count(distinct 'SPECIES NAME'n ) as Number_of_Species
  from mysas.MyData;
quit;

Re: Counting distinct inputs in a column using proc sql

unwashedhelimix — Tue, 03 Dec 2024 07:57:39 GMT

I tried this, but it looks like I am getting an error: ERROR: The following columns were not found in the contributing tables: "SPECIES NAME"n

However, when I remove the n, it runs again giving me the output of 1. I also added

option validvarname=any;

before the proc sql.

Re: Counting distinct inputs in a column using proc sql

Ksharp — Tue, 03 Dec 2024 08:14:03 GMT

So using the following code to check if your dataset include this variable:

proc contents data=mysas.MyData varnum;
run;