I need help reading ODS CSV file back into SAS

[JMagenta]

Hello,

So thank you in advance. I used the following code to preserve leading zeros when I exported my CSV file:

ods csv file="&outpath.Need.csv"; ods csv options (QUOTE_BY_TYPE='Yes' PREPEND_EQUALS='Yes' sheet_name='Need' embedded_titles='yes') style=daisy;
proc print data=need;
run;
ods csv close;

This code worked. However now I need to read that same file back into SAS without the appended quotes and equal signs. Is there code to take the quotes and equal signs off all the numeric data, basically reversing what I did without manually stripping each variable? There are 100 plus variables that now have equal signs and quotes around them.

I thought I was on to something trying to import using proc document but I guess I was wrong.  Please message me if you need more information and I will do my best.

[ballardw]

Before we start, did you open that file in a spread sheet program and then save it? Spreadsheets are know to change the values when opening CSV files.

You would be well served by copying 5 or so lines from the file and pasting them into a text box opened on the forum with the </> icon that appears above the message windows.

When I run this code which uses a data set you have access to (don't run unless you change the location or name of the CSV file)

ods csv file="&outpath.Need.csv"; 
ods csv options (QUOTE_BY_TYPE='Yes' 
    PREPEND_EQUALS='Yes' sheet_name='Need' 
    embedded_titles='yes') style=daisy;
proc print data=sashelp.class;
run;
ods csv close;

And read it back with

data want;
  infile  "&outpath.Need.csv" dlm=',' missover dsd firstobs=2 ;
  informat
   Obs   $3.
   Name  $8.
   Sex   $1.
   Age    8.
   Height 8.
   Weight 8.
   ;
   input
   Obs  
   Name  
   Sex   
   Age   
   Height
   Weight
   ;
run;

The only resulting = is in the OBS variable, which was not in the original data set and could be dropped from the result. If you are running a different set up your output may vary and then an example of YOUR file becomes critical.

The DSD means that the quotes are treated to protect embedded delimiters in values and will be effectively ignored around a text value.

Since you mentioned "preserve leading 0" then you know that the values have to be read as character, correct?

[JMagenta]

Thank you,

I see what you mean. However my own dataset does come back with numbers like this: ="003" 

And it's like that for all of the numeric variables. Is there another way that I can export the file so that it is readable by Excel, 

keeps the leading and trailing zeroes, and is read back into SAS after data entry with simple proc import statement?

J

[Tom · Accepted solution]

Why did you use that strange option to add the equal signs if you don't want them?

And it does not even seem to work properly (At least with SAS 9.4m5 release) it skips the = on the first observation.

filename excelcsv temp;
ods csv file=excelcsv
  options (QUOTE_BY_TYPE='Yes' PREPEND_EQUALS='Yes' sheet_name='Need' embedded_titles='yes') 
  style=daisy
;
proc print data=sashelp.class(obs=5);
run;
ods csv close;

data _null_;
  infile excelcsv ;
  input ;
  put _infile_;
run;

"Obs","Name","Sex","Age","Height","Weight"
"1","Alfred","M",14,69.0,112.5
="2","Alice","F",13,56.5,84.0
="3","Barbara","F",13,65.3,98.0
="4","Carol","F",14,62.8,102.5
="5","Henry","M",14,63.5,102.5

If you want to convert that to a more normal CSV file then just remove the leading equal signs.  You might as well remove the unneeded quotes also.

filename csv temp;
data _null_;
  infile excelcsv length=ll column=cc dlm=',' dsd truncover ;
  file csv dsd;
  do until(cc>ll);
    input string :$200. @ ;
    if string=:'="' then string=dequote(substr(string,2));
    put string @;
  end;
  put;
run;

data _null_;
  infile csv;
  input;
  put _infile_;
run;

Now you have something that looks like a normal CSV file.

Obs,Name,Sex,Age,Height,Weight
1,Alfred,M,14,69.0,112.5
2,Alice,F,13,56.5,84.0
3,Barbara,F,13,65.3,98.0
4,Carol,F,14,62.8,102.5
5,Henry,M,14,63.5,102.5

If you want to keep those extra quotes you could do this instead:

data _null_;
  infile excelcsv length=ll column=cc dlm=',' dsd truncover ;
  file csv dsd;
  do until(cc>ll);
    input string :$200. @ ;
    if string=:'="' then do;
      string=dequote(substr(string,2));
      put string ~ @;
    end;
    else put string @;
  end;
  put;
run;

Result

Obs,Name,Sex,Age,Height,Weight
1,Alfred,M,14,69.0,112.5
"2",Alice,F,13,56.5,84.0
"3",Barbara,F,13,65.3,98.0
"4",Carol,F,14,62.8,102.5
"5",Henry,M,14,63.5,102.5

Notice how it was unable to correct the mistake of not putting the = before the value of OBS on the first observation.

[JMagenta]

Hello,

Thank you. I found this code on the forum to keep leading zeros. What you did seemed to fix everything! However, how do I get the file back into a SAS dataset from there? I changed the "data _null_" statement to try and give me back a reasonable dataset and was unsuccessful. I know I'm missing something.

Thank you,

J

[Tom]

You can use PROC IMPORT if you have to GUESS what the variables are. It has a nasty habit of forcing columns where every value is enclosed in quotes as a character variable.  You might even like that "feature" (looks more like a bug to me).

But if you KNOW what the variables are skip the guessing game and just write the data step yourself.

Let's take my little experiment result as an example:

Obs,Name,Sex,Age,Height,Weight
1,Alfred,M,14,69.0,112.5
"2",Alice,F,13,56.5,84.0
"3",Barbara,F,13,65.3,98.0
"4",Carol,F,14,62.8,102.5
"5",Henry,M,14,63.5,102.5

So I see there are 6 variable there. So I just need to define the variables and then read them.

data want;
  infile csv dsd firstobs=2 truncover;
  length obs 8 Name $10 Sex $1 Age Height Weight 8 ;
  input obs -- weight;
run;

If any of the variables were DATE (or TIME or DATETIME) values I might need to add a FORMAT and/or an INFORMAT statement to tell SAS to use special instructions for writing and/or reading the values. But for normal numbers and strings no formats or informats are needed.

Results

Obs    obs     Name      Sex    Age    Height    Weight

 1      1     Alfred      M      14     69.0      112.5
 2      2     Alice       F      13     56.5       84.0
 3      3     Barbara     F      13     65.3       98.0
 4      4     Carol       F      14     62.8      102.5
 5      5     Henry       M      14     63.5      102.5
 6      .                         .       .          .

Looks like ODS CVS added an extra empty line to the bottom of the file. You could probably remove that easily enough.

[JMagenta]

Thank you so much for your help!

J

[Tom]

---

@JMagenta wrote:

Hello,

Thank you. I found this code on the forum to keep leading zeros. What you did seemed to fix everything! However, how do I get the file back into a SAS dataset from there? I changed the "data _null_" statement to try and give me back a reasonable dataset and was unsuccessful. I know I'm missing something.

Thank you,

J

---

Looks like you are trying to hack the way that Excel reads the CSV file with those equal signs. To really control how Excel reads a CSV file do NOT let it open the file automatically.  Instead use the Excel tools for loading data from a text file where you have the ability to tell it what type of cells to create for each variable.

Or better still just write the data directly to an XLSX file and the TYPE of the variables will be preserved.

[JMagenta]

Wow, you're right! It preserves the variable type. I kept my zeros.

Thank you!

J