topic Proc Sql and year function in SAS Procedures

Proc Sql and year function

Hammis — Wed, 19 Aug 2009 01:33:02 GMT

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.

Re: Proc Sql and year function

ChrisNZ — Wed, 19 Aug 2009 03:24:42 GMT

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.

Re: Proc Sql and year function

sbb — Wed, 19 Aug 2009 13:16:17 GMT

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