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
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.
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.
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
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.