BookmarkSubscribeRSS Feed
Azeem112
Quartz | Level 8

I have an excel file that I'm importing into SAS using proc import. 

 

proc import datafile="Transactions_Details_Sample_Test.xlsx" out=Transaction_dump dbms=xlsx replace;
run;

 

Assume the excel file has 1000 rows with below columns

Employee Name(Char Data) | NIN Number (Number Data) | Enrollment Date (Date Data)   

 

The Issue I'm facing

if someone enters the character data even by mistake or typo into the 1000th row of excel of the NIN Number column, the SAS will treat it as a character column, and all the numeric operations will fail. How can I write code in SAS to always treat it as a numeric column and to find that character data?

 

The same issue is with date, is by typo someone enters Jann-2021 instead of Jan-2021 the SAS will treat it as a character column instead of numeric date column and all the data will be messed up.

 

Attached is a sample of data, the last row has type errors that can change the data type of column in SAS. with millions of rows, I want to find such errors in my code.

3 REPLIES 3
Kurt_Bremser
Super User

To avoid the guessing of PROC IMPORT, save the spreadsheet to a csv file and read that with a data step written by yourself; add code to detect input errors (automatic _ERROR_ variable).

Azeem112
Quartz | Level 8

If I export that into CSV and there is any comma in the name, it's gonna mess up. 

Kurt_Bremser
Super User

Excel uses semicolons in csv exports, and puts quotes around values that contain delimiters. The DSD option in the INFILE statement will honor that.

See the attached Excel and csv file.

I read the csv and print the dataset with

data test;
infile "~/test.csv" dlm=";" dsd truncover;
input name :$20. data $ number;
run;

proc print data=test;
run;

giving this result:

Beob.	name	data	number
1	name,name	xxx	1
2	name;name	yyy	2

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 331 views
  • 0 likes
  • 2 in conversation