topic Re: Proc sql datatypes in create table statement in SAS Programming

Proc sql datatypes in create table statement

PrinceAde — Wed, 22 May 2024 14:02:29 GMT

Please I need clarifcation on the code below.
Thanks.

"data-type bis enclosed in parentheses and specifies one of the following: CHARACTER (or CHAR)
| VARCHAR | INTEGER (or INT)."

proc sql;
create table work.discount
(Destination char(3),
BeginDate num Format=date9.,
EndDate num format=date9.,
Discount num);
quit;

I expect the data-type should be in parenthesis based on the syntax description above, e.g, Destination (char)(3).
Please what am I missing?

Re: Proc sql datatypes in create table statement

ballardw — Wed, 22 May 2024 14:29:37 GMT

I am not sure what "needs clarification".

If you try to place the type in () as you suggest you generate multiple errors

75   proc sql;
76   create table work.discount
77   (Destination (char)(3),
                  -      -
                  22     200
                  200
ERROR 22-322: Syntax error, expecting one of the following: CHAR, CHARACTER, DATE, DEC, DECIMAL,
              DOUBLE, FLOAT, INT, INTEGER, NUM, NUMERIC, REAL, SMALLINT, VARCHAR.

ERROR 200-322: The symbol is not recognized and will be ignored.

                           -
                           76
ERROR 76-322: Syntax error, statement will be ignored.

77 ! (Destination (char)(3),
                         -
                         22
ERROR 22-322: Syntax error, expecting one of the following: a name, keyword, CONSTRAINT.

77 ! (Destination (char)(3),
                           -
                           22
ERROR 22-322: Syntax error, expecting one of the following: CHAR, CHARACTER, DATE, DEC, DECIMAL,
              FLOAT, INT, INTEGER, NUM, NUMERIC, REAL, SMALLINT, VARCHAR.

78   BeginDate num Format=date9.,
79   EndDate num format=date9.,
80   Discount num);
81   quit;

The underscore above the 22 200 error shows that the "(" before the keyword CHAR is an error.

The syntax is Variable type and length optional for CHAR variables.

If you talking about the awkwardly phrased statement in quotes the parentheses are around the whole construct of ( variable1 type, variable2 type , variable3 type )

Re: Proc sql datatypes in create table statement

PrinceAde — Wed, 22 May 2024 14:35:15 GMT

Thanks for the reply.

Yes it generated error. The documentation states that the "data-type is enclosed in parentheses".
The data-type for destination for example is char.

Is it that the documentation has been updated?

Re: Proc sql datatypes in create table statement

ballardw — Wed, 22 May 2024 14:47:35 GMT

Which documentation? Can you post a link to it?

https://documentation.sas.com/doc/en/pgmsascdc/v_051/sqlproc/n0v236a0ti4nmen1mmw4gexjd0tg.htm

Shows the syntax as

Column data-type <column-modifier(s)>

no parentheses around data-type.

Re: Proc sql datatypes in create table statement

PrinceAde — Wed, 22 May 2024 15:02:59 GMT

Sorry, sir. I saw in the Text; SAS® Certified Professional Prep Guide: Advanced Programming Using SAS® 9.4

Re: Proc sql datatypes in create table statement

Patrick — Thu, 23 May 2024 04:50:23 GMT

@PrinceAde wrote:

Sorry, sir. I saw in the Text; SAS® Certified Professional Prep Guide: Advanced Programming Using SAS® 9.4

May-be share the section that confuses you and we can share how we understand it.

In SAS9.4 the only data types available for SAS tables are CHAR and NUMERIC. I didn't even know that Proc SQL allows for other datatypes - but they all result in type CHAR and NUM.

proc sql;
  create table work.sample
  (
    var1 char(20)
    ,var2 varchar(20)
    ,var3 integer
    ,var4 smallint
    ,var5 decimal
    ,var6 numeric
    ,var7 float(10,2)
    ,var8 real
    ,var9 double precision
    ,var10 date
  )
  ;
quit;

proc contents data=work.sample;
run;quit;

It looks like when requesting a DATE type SAS also adds a DATE. format which is useful.

Re: Proc sql datatypes in create table statement

PrinceAde — Thu, 23 May 2024 08:54:59 GMT

Thank you sir. If you can read the line that describe the data-type.

Re: Proc sql datatypes in create table statement

Tom — Thu, 23 May 2024 13:26:40 GMT

You can report this mistaken wording to SAS and they will fix it pretty quickly.

Each documentation page includes a FEEDBACK link which will open a email message directed to the documentation support team. They are very responsive.

Re: Proc sql datatypes in create table statement

ballardw — Thu, 23 May 2024 14:58:39 GMT

Do note that the line in the body of that starts Column-definition under the Column-specification does not show () around data type. Note that it does show the () around the conditional width. So if the code should be (char)(3) the line would have shown the () around the data type.

The data-type lines do not match the syntax at the header.

I tend to pay a bit more attention to the syntax statement examples at the start of section than the detail text as quite often when I'm looking at such documents I need a refresher and not reading from scratch. I have also in the past resolved a few such discrepancies between diagrams and text that was a tad confusing (Sortseq options in Proc sort for example when they first came out)