SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Reading blank variables in a csv into SAS as numeric.

Reply
Contributor
Posts: 30

Reading blank variables in a csv into SAS as numeric.

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.  

Respected Advisor
Posts: 3,799

Re: Reading blank variables in a csv into SAS as numeric.


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.

Contributor
Posts: 30

Re: Reading blank variables in a csv into SAS as numeric.

Posted in reply to data_null__

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?

Respected Advisor
Posts: 3,799

Re: Reading blank variables in a csv into SAS as numeric.


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?

Contributor
Posts: 30

Re: Reading blank variables in a csv into SAS as numeric.

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.   

Super User
Posts: 11,343

Re: Reading blank variables in a csv into SAS as numeric.

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.

 

Contributor
Posts: 30

Re: Reading blank variables in a csv into SAS as numeric.

[ Edited ]

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. 

Ask a Question
Discussion stats
  • 6 replies
  • 186 views
  • 2 likes
  • 3 in conversation