How to loading variables which are huge in length?

[pepevo]

Hello experts.

How can I format the column countt/column in huge length in proc sql.

Normally in Oracle we set declare the column in a new format like below:

col fieldname a10;

col fieldname1 9999999;

I have tried  dbsastype=(column='CHAR(10)'))  and didn't work

I have queries:

select count(*) from table this one give me 1.8787E8 

select count(*) from table1 a inner join table2 b on a.col = b.col inner join table3 c on c.col1=b.col2; the result is 1.8787E8

select column1, column2, column3 from table1 a inner join table2 b on a.col = b.col inner join table3 c on c.col1=b.col2;

how can I  pull all the scientific number in proc sql?

thank you,

Bach-Nga

[ballardw · Accepted solution]

The values you show like

select count(*) from table this one give me 1.8787E8 

are numeric so attempting to set a character type is inappropriate

You should be able to use

select count(*) format f12.

to display 12 digits.

[pepevo]

thank you for replying, I have tried to set at the beginning and it didn't work earlier.

now I need to set next to the column needed to select.  

what about character length?  Do I need to set the format next to the column too.  I did 'format column n10.; ' it didn't work

v/r,

Bach-Nga

[Tom]

SAS only has two data types. Floating point numbers and fixed length character strings.

The default in PROC SQL for numbers is to use BEST8. 

You can attach a FORMAT to your variable to help SAS understand how you want the values displayed.

For your simple queries that looks like:

select count(*) format=best32. from table;

If you know the numbers will always be integers then you might use the 32. format (also known as the F32. format).

But you might instead want to use the COMMA format to make it easier for humans to read.

[pepevo]

thank you, Mr. ballardw already showed me how to format the count and it worked

I still concern the character column as same as Oracle:

"

col name a25

col address a30

select name, address from users;

"

I did the same in proc sql and it didn't work;

select name format n25., address format n30. from users;

thank you,

Bach-Nga

[Tom]

That doesn't make any sense in SAS syntax. First of all there is no need to attach a format to character variable since SAS already will display the character variable properly.  But if you did want to attach a format you need to use a character format, such as $25.

If you want to define the LENGTH of the variable use the LENGTH= option instead. 

So if your source database defined NAME with a length of 4000 and you KNOW that you only need the first 25 bytes then you can use the LENGTH= option after the variable name to tell SAS to define the new variable as being only 25 bytes long.

select name length=25 ....
from mydb.mytable

You might need to also set the format as SAS has a nasty habit of attaching formats to character variables pulled from external databases.  It would be strange to have name defined as length 25 but with the $4000. format attached to it.