BookmarkSubscribeRSS Feed
andrewfau
Fluorite | Level 6

I imported parcel ids for different counties from xlsx files to SAS files. One county shows the parcel ids as numeric data and uses normal notation. One county shows the parcel ids as character data and uses standard notation, and the last county shows the parcel ids as numeric data and has them in scientific notation. 

 

Question 1: Is there a quick way I can change the scientific notation to standard notation without having to reimport the files?

 

Question 2: Is there a quick way I can change the parcel ids from numeric to character data without losing digits or having it reformat?

11 REPLIES 11
HB
Barite | Level 11 HB
Barite | Level 11

What does the data in scientific notation look like in SAS? How is it being stored?

 

1.257E3

??

 

andrewfau
Fluorite | Level 6

3.84344160600502E+16

 

The parcel code is 384344160600502-- but the last 2 digits are excluded. 

 

Other examples:

 

3.8434415160470096E+16

3.843441636E+16

 

My assumption is that the lengths differ because of zeros and whether or not the notation considers digits to be significant.

SASKiwi
PROC Star

Looks like your IDs are up to 16 digits long. In that case you MUST import them as character data as SAS numeric variables can only only hold 15 digits accurately. 

Reeza
Super User

Look at the variable TYPE and FORMAT.

 

If it's just the format you can change that easily, use a FORMAT statement.

 

If its the type you need to convert it, using either PUT or INPUT.

andrewfau
Fluorite | Level 6

it is Character with a $22. format

SASKiwi
PROC Star

If the number of spreadsheets is small I would convert all of the parcel ID columns to text in Excel then re-import them into SAS.

 

A more reliable way would be to save the Excel sheets as CSVs, then import those into SAS. Here you can use a DATA step to enforce the IDs to be character.

 

I idea is to get the parcel ID columns all importing as character to begin with so you don't need to manipulate them afterwards. To me this is far preferable to dealing with unpredictable data types in SAS.

 

 

andrewfau
Fluorite | Level 6

I initially imported them as CSVs but had a problem with a different code that was being imported as numeric in some and character in others. I converted them all to xlsx and reimported them. The problem, I think, is I am downloading data from different counties and they have variances in how they store the data. I may have to go back to the beginning and do it all over again though.

Patrick
Opal | Level 21

@andrewfau

Just did a test with Excel 2013 and I haven't been able to impute a number with 22 digits with full precision into an Excel cell. 

I have been able to impute all the digits when defining the cell as TEXT.

 

When using the TEXT format on the Excel cell then the following worked like a charm:

libname pid xlsx 'c:\temp\parcel_id.xlsx';

Capture.JPG

ballardw
Super User

If you are using Proc Import to read Excel files you basically don't have much in the way of control over the guessing process Proc Import uses.

 

For consistent data you will likely need to use a process with more control. One way is to save the Excel data as CSV files and use a common data step program to read the data changing the source file and result data set names. That way you can control the types of variables and ensure that the length of character variables stay the same.

 

Changing numeric to character would involve something like:

 

Data new;

    set old (rename=(parcel_id = old_id));

   parceled = put(old_id, best10. -L);

run;

assuming 10 characters is enough to hold the character version, if not increase the 10 to the desired length. ONLY use this if parcel_id, or whatever your actual variable name may be is numeric without decimals.

andrewfau
Fluorite | Level 6

So I went ahead and reimported them all as CSVs and that solved the problem with the parcel_id. However, I remember now why I switched to XLSX in the first place. When importing as CSVs other data lost digits in other places. 

 

Here is a screen shot of my SAS file from the CSV import:

 

bigger (2).jpg

 

Here is a screen shot from the SAS file of my XLSX import:other (2).jpg

 

The CSV import has them all as Character and only keeps the first digit. The XLSX file has them as numeric and has the full value. All of them should be character except for sale_prc1. I am thinking all I need to do is some sort of format change, but am not quite sure how. I want to change the sale_prc1 to a numeric variable and accommodate for up to 10 digits. I want to keep those other variables at character but get all the digits to show. 

SASKiwi
PROC Star

How are you importing your CSVs? Please post your code if you are using something like PROC IMPORT.

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
  • 11 replies
  • 2294 views
  • 3 likes
  • 6 in conversation