Need count by monthyear

[Cho8]

iam getting error from below query

date format yymmdd10.(12/30/2020) in the table.

any help appreciated

select put(date,yymmn6.)as monyr,count(*) from table
group by monyr
order by monyr desc;

[Cho8]

need volumes/counts by month for 3 years

[PaigeMiller · Accepted solution]

Try this:

select put(date,yymmn6.)as monyr,count(*) from table group by calculated monyr
order by calculated monyr desc;

In the future, when you get an error message, then 100% of the time (no exceptions, and don't wait until we ask) you need to show us the LOG for this PROC, the entire log for this PROC, all of it, every single character of the log for this PROC, do not select and choose parts of the log for this PROC. That way we can see the code, the ERROR message(s), the WARNING message(s) and the NOTE message(s). Copy the LOG as text and paste it into the box that appears when you click on the </> icon.

And if my correction to your code above doesn't fix the problem, then show us the log exactly as I have explained.

[Cho8]

ERROR: Teradata prepare: Syntax error: Data Type "date" does not match a Defined Type name. SQL statement was: select

       Put(date,yymmn6.)as monyr,count(*) from table group by calculated monyr order by calculated

       monyr desc;.

[PaigeMiller]

@Cho8  we are trying to help you, but you have to help us

Repeating:

you need to show us the LOG for this PROC, the entire log for this PROC, all of it, every single character of the log for this PROC, do not select and choose parts of the log for this PROC. That way we can see the code, the ERROR message(s), the WARNING message(s) and the NOTE message(s). Copy the LOG as text and paste it into the box that appears when you click on the </> icon.

[Cho8]

PROC SQL;

27         connect to XXX(database=XXXX tdpid=XXXX user=XXX password=”XXXX");

28         create table XXX as

29         select * from connection to XX

30         (

31         select put(date,yymmn6.)as monyr,count(*) from XXXXXXXXX

32        

33         group by calculated monyr

34         order by calculated monyr desc;

35        

36         );

ERROR: Teradata prepare: Syntax error: Data Type "date" does not match a Defined Type name. SQL statement was: select

       Put(date,yymmn6.)as monyr,count(*) from XXX.table_name group by calculated monyr order by calculated

       monyr desc;.

[novinosrin]

Hi @Cho8  The code block of yours -

30         (

31         select put(date,yymmn6.)as monyr,count(*) from XXXXXXXXX

32        

33         group by calculated monyr

34         order by calculated monyr desc;

35        

36         );

is actually for writing DBMS specific SQL aka pass through for open database connectivity or in simple terms is a SAS Access to database tables. However, in your case it is apparent you have written Proc SQL which is SAS's proprietary SQL. Therefore, the error is rather obvious.

Option 1: Write DBMS specific query i.e. Teradata SQL

Option 2: Extract using DBMS specific SQL in code block and then write another step using Proc SQL

HTH & Kind Regards!

NS

[PaigeMiller]

Another solution

proc freq data=have;
     tables date;
     format date yymmn6.;
run;