Reading blank variables in a csv into SAS as numeric.

[proctice]

So I found this macro on the sas website that cleans csvs using hexidecimal notation. 

http://support.sas.com/kb/26/065.html

 I would like to modify this code to exchange character blanks with numeric blanks or periods, the same way it changes character returns to @ signs.    Is this possible? Thanks.

If not, is there another way to force proc import to read empty variables as numeric or should I resort to using infile instead of proc import. 

Thank you.  

[data_null__]

---

@proctice wrote:

So I found this macro on the sas website that cleans csvs using hexidecimal notation. 

http://support.sas.com/kb/26/065.html

@ I would like to modify this code to exchange character blanks with numeric blanks or periods, the same way it changes character returns to @ signs.    Is this possible? Thanks.

If not, is there another way to force proc import to read empty variables as numeric or should I resort to using infile instead of proc import. 

Thank you.  

---

I don't believe your problem will require such a large hammer.  If you read the CSV using INFILE you can define the variables explicitly not guessing by PROC IMPORT needed.  You can use PROC IMPORT to create a program that you can modify to change variable; type, informat, format, length etc.

[proctice]

I was already using the cleancsv macro so if it could be done, it would be an easy fix. 

I am also using an importcsv macro that reads all csvs in a directory even ones with very different layouts.  This is great since I am reading hundreds of csvs.  This macro has a proc import in it.  With infile, I will have to write separate code for each different layout, so I was trying to avoid it.    

Another way to do it would be to drop variables with all missing data.  Do you know of any macros that do that?

[data_null__]

---

@proctice wrote:

I was already using the cleancsv macro so if it could be done, it would be an easy fix. 

I am also using an importcsv macro that reads all csvs in a directory even ones with very different layouts.  This is great since I am reading hundreds of csvs.  This macro has a proc import in it.  With infile, I will have to write separate code for each different layout, so I was trying to avoid it.    

Another way to do it would be to drop variables with all missing data.  Do you know of any macros that do that?

---

Do you have common fields that have the same attributes across all CSVs where the fields may be in a different order or omitted from one or more CSVs?  Do you want to read all data from the CSVs into one SAS data set?

[proctice]

There are groups of csvs that are alike, but they are not all alike.  I want to put the groups into data sets together, but when one file is missing data for a variable proc import reads it as character and datasets that are not missing the variable have the variable as numeric.  I am trying to see if I can do something with append and force.  Infile is my back-up plan, but I am trying to avoid it.   

[ballardw]

I can tell you right now that APPEND with different variable types will fail with or without FORCE.

Force will allow you to append a data set that has different types but the character varaibles appending to same named numerics will not convert a value to numeric.

Brief example:

data one;
   x= 1;
run;
data two;
   x='3';
run;

proc append base=one data=two force;run;

Result: warning message and missing value for the variable from the append data set.

[proctice]

That still might work since the data being read as character is missing to begin with (assuming proc import only got the type wrong for the missing data), so changing it from missing character to missing numeric is exactly what I want to do.   I just have to make sure the first dataset I read has the variable as numeric.   My initial question was whether this could be done using the hexadecimal values in the cleancsv macro above.