BookmarkSubscribeRSS Feed
prakashpk
Calcite | Level 5

I have numeric variable with decimals

data l;
input number;
cards;
54498.014
17188.12647
54545
211151.127287128718
run;

need to see the length of numeric variable in SAS SQL commond without put commond or with commond along with suppressing addional zeros.

Can any one help me to code this in SAS SQL please?

 

Number                             Length
54498.014                         9
17188.12647                    11
54545                                5
211151.127312345           16

123456789012345678.1  20      

 

6 REPLIES 6
Kurt_Bremser
Super User

At least your last example won't work, as SAS numeric values have a precision of ~15 digits.

Google "SAS numeric precision" for in-depth information.

Otherwise, you can only determine the length from strings, and therefore will need the put() function.

Rwon
Obsidian | Level 7

Adding to Kurt,

 

You can read in the data as a character variable and just use the length function in the data step.

data l;
input number $50.;
number_length = length(number);
cards;
54498.014
17188.12647
54545
211151.127287128718
123456789012345678.1
;
run;

 

 

prakashpk
Calcite | Level 5

Thank you Rwon for your reply 🙂

 

Actually i know how to write in Data step, but i want to understand how it is written in SAS SQL format as i have data which comes from the server and need to see one of the attribute length, whether it is exceeding 19 which includes . and decimals. or number contains more than 6 decimal numbers. can you please send me SAS SQL code which i can use for this case.

 

Regards,

Prakash

Kurt_Bremser
Super User

I guess that by "server" you mean a database system? In that case, you have a problem, as 19 digits go beyond the maximum precision available to SAS (~15 digits, as already mentioned), and numbers like that will not be correctly represented in SAS anyway.

I suggest you do a typecast in the database (SQL pass-through), and import into SAS as strings (character).

Or you have the table exported to a flat file from the DBMS, and then read the column as character with a SAS data step.

ChrisBrooks
Ammonite | Level 13

Adding to Kurt

 

If you need greater precision you might try using SAS DS2 - references here and here

ChrisNZ
Tourmaline | Level 20

The closest you'll get using proc sql is:

select length(NUMBER,best32. -l) from HAVE;

but that's a weird request, and you'll get surprises due to numeric precision as explained above.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 8376 views
  • 0 likes
  • 5 in conversation