BookmarkSubscribeRSS Feed
PrinceAde
Obsidian | Level 7

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?

8 REPLIES 8
ballardw
Super User

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 )

PrinceAde
Obsidian | Level 7

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?

ballardw
Super User

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.

PrinceAde
Obsidian | Level 7

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

Patrick
Opal | Level 21

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

Patrick_0-1716439653226.png

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

PrinceAde
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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.

ballardw
Super User

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)

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 545 views
  • 2 likes
  • 4 in conversation