Proc Sql and year function

[Hammis]

I am an sql user trying to learn SAS and would appreciate it if anyone would help me with this query.

The following code I expected only to bring back the unique dates and there counts.... but running this in SAS returned a record for every observation with the year and count.

proc sql;
select year(change_date),count(*)
from customer_table
group by year(change_date);
quit;

Example result:

Year Count
2009 10000
2009 10000
2009 10000
2009 10000
...
...

What would be the correct way to write this query?
Cheers.

[ChrisNZ]

You are grouping by a fct of change_date, but change_date in not in your putput.

select year(change_date) as year, count(*)
from customer_table
group by year;

will work.

[sbb]

You mention "only want to bring back the date", yet you have the YEAR function coded? Interesting.

You will also want to review PROC SQL procedure discussion in the SAS documentation, available at the SAS support http://support.sas.com/ website, along with other topic-related technical papers and conference presentations on relevant topics.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473669.htm