BookmarkSubscribeRSS Feed
proctice
Quartz | Level 8

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.  

6 REPLIES 6
data_null__
Jade | Level 19

@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
Quartz | Level 8

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__
Jade | Level 19

@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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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. 

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1798 views
  • 2 likes
  • 3 in conversation