DATA Step, Macro, Functions and more

SAS Import

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

SAS Import

Hi,

 

I am attempting to import multiple CSV files (>500) into SAS and they all have the same variable formats.

There are a total of 16 variables of which 2 should be character and 14 should be numeric.

 

I have tried doing a proc import, however all fields were assigned to character by default.

 

I then moved onto the data infile method and renamed the 14 variables from character ($4.) to best.

This is causing problems as the data for 4/14  variables contains a '-' where there is no number present.

 

For example (one variable):

Volume
1,096,532
-
63,741
978,134

 

How do I go about setting the numeric format? Is there another format besides 'best.' that can be used to accomodate for the '-'?

 

Any help would be greatly appreciated.

 

SAS log shows:

NOTE: Invalid data for Volume in line 16 52-57


Accepted Solutions
Solution
a month ago
Super User
Super User
Posts: 8,274

Re: SAS Import

[ Edited ]
Posted in reply to PetePatel

First thing is don't use PROC IMPORT for files that you already KNOW the format.  It will make guesses at how to define each variable. And it will make that guess for each file independently. Write your own data step to read the file.  You can even read all of them in one data step if you want.

 

To your question about how to read in a hyphen as meaning a missing value there here are some simple solutions.

1) Just let SAS change it to missing for you.  You can add the ? or ?? modifiers to the INPUT (or INPUT() function) to suppress the errors.

data want ;
  infile 'myfile.csv' dsd truncover firstobs=2 ;
  input volume :??comma. ;
run;

2) Read it as character and program around the value.

data want ;
  infile 'myfile.csv' dsd truncover firstobs=2 ;
  input volume_raw :$20. ;
  if volume_raw not in ('-',' ') then volume=input(volume_raw,comma32.) ;
run;

3) Make your own informat and use that.

proc format ;
  invalue hyphen '-'=. other=[comma32.] ;
run;

data want ;
  infile 'myfile.csv' dsd truncover firstobs=2 ;
  input volume hyphen.;
run;

 

View solution in original post


All Replies
Super User
Posts: 10,568

Re: SAS Import

[ Edited ]
Posted in reply to PetePatel

Numbers like that are read with the comma. informat. You will only get a NOTE for invalid data for the hyphen. If you want to avoid that, do

input _number :$20.;
if _number ne '-' then number = input(_number,comma20.);
drop _number;

(it is recommended to make your code clean so that it does not throw NOTEs as mentioned above in normal operation)

 

Edit: added a missing underline in the input function

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 629

Re: SAS Import

Posted in reply to PetePatel

Using

options dsoptions=note2err;

turns all unexpected notes into error messages.

Solution
a month ago
Super User
Super User
Posts: 8,274

Re: SAS Import

[ Edited ]
Posted in reply to PetePatel

First thing is don't use PROC IMPORT for files that you already KNOW the format.  It will make guesses at how to define each variable. And it will make that guess for each file independently. Write your own data step to read the file.  You can even read all of them in one data step if you want.

 

To your question about how to read in a hyphen as meaning a missing value there here are some simple solutions.

1) Just let SAS change it to missing for you.  You can add the ? or ?? modifiers to the INPUT (or INPUT() function) to suppress the errors.

data want ;
  infile 'myfile.csv' dsd truncover firstobs=2 ;
  input volume :??comma. ;
run;

2) Read it as character and program around the value.

data want ;
  infile 'myfile.csv' dsd truncover firstobs=2 ;
  input volume_raw :$20. ;
  if volume_raw not in ('-',' ') then volume=input(volume_raw,comma32.) ;
run;

3) Make your own informat and use that.

proc format ;
  invalue hyphen '-'=. other=[comma32.] ;
run;

data want ;
  infile 'myfile.csv' dsd truncover firstobs=2 ;
  input volume hyphen.;
run;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 75 views
  • 0 likes
  • 4 in conversation