topic Re: Need count by monthyear in SAS Programming

Need count by monthyear

Cho8 — Thu, 01 Jul 2021 16:12:17 GMT

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;

Re: Need count by monthyear

Cho8 — Thu, 01 Jul 2021 16:25:42 GMT

need volumes/counts by month for 3 years

Re: Need count by monthyear

PaigeMiller — Thu, 01 Jul 2021 16:34:32 GMT

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.

Re: Need count by monthyear

PaigeMiller — Thu, 01 Jul 2021 16:39:05 GMT

Another solution

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

Re: Need count by monthyear

Cho8 — Thu, 01 Jul 2021 16:47:39 GMT

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

Re: Need count by monthyear

PaigeMiller — Thu, 01 Jul 2021 16:52:31 GMT

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

Re: Need count by monthyear

Cho8 — Thu, 01 Jul 2021 17:08:47 GMT

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

33 group by calculated monyr

34 order by calculated monyr desc;

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

Re: Need count by monthyear

novinosrin — Thu, 01 Jul 2021 17:24:51 GMT

Hi @Cho8 The code block of yours -

30 (

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

33 group by calculated monyr

34 order by calculated monyr desc;

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!