Proc Import fails

[akhq]

I am losing my mind over proc import... I would need to export a sas dataset into a CSV (or xls/x doesn't matter), then I would modify the file and import it back to sas. The issue is that if I add a row with new values then everything looks ok in excel, but proc import fails to import rows or even columns without any error/warning... I have tried to convert all the date variables and numeric variables into a format of 'value so that excel would consider them character, however what happens is that if I add new values then the ' will be disappearing automatically and the raw csv will also be saved without the '. I hacked this so that when filling in excel I then use '' to get single ' into the csv, and that works, however the import still fails. I have attached an testfile (last row is added after the export) and a code I have used is the following:

proc import file="&path./testdata_tomod.csv" dbms=csv replace out=import_testing; delimiter=";";run;

Then I also know that I could read the CSV using infile, but the issue is that I need to retain the original metadata for the table, and those are all lost in the process. And for example I could have in original data a value of "0000005151" CHAR and then proc import reads it in as 51515 NUM and I have no metadata in the original table to convert that back to the as stated value with correct format of zX. . 

So

a) what the heck is happening there?

b) is there any way to get dates and localisation issues handled without doing extensive manipulation and back and forth labelling etc etc.? (In EU localisation to excel uses "," as decimal delimiter instead of ".")

[sbxkoenk]

Home > Programming > SAS Procedures > PROC IMPORT/EXPORT -- forcing column to be character or numeric
https://communities.sas.com/t5/SAS-Procedures/PROC-IMPORT-EXPORT-forcing-column-to-be-character-or-n...

or a Google search :

proc import SAS read in all columns as character site:communities.sas.com

Koen

[Kurt_Bremser]

Never use PROC IMPORT for csv files. Write the DATA step yourself.

Particularly in a case like this, where you know the structure already and can use it:

filename testfile temp;

data _null_;
set sashelp.class;
file testfile dlm="," dsd;
if _n_ = 1 then put "Name,Sex,Age,Height,Weight";
put name--weight; * from first to last variable as physically ordered in the dataset;
run;

data class;
if 0 then set sashelp.class; * populates the PDV and sets attributes;
infile testfile dlm="," dsd firstobs=2;
input name--weight; * see above;
run;

filename testfile;

One caveat: your dataset must have the informats defined for variables which are not simply character or numeric (e.g. dates and times).

[Reeza]

---

@akhq wrote:

Then I also know that I could read the CSV using infile, but the issue is that I need to retain the original metadata for the table, and those are all lost in the process. And for example I could have in original data a value of "0000005151" CHAR and then proc import reads it in as 51515 NUM and I have no metadata in the original table to convert that back to the as stated value with correct format of zX. . 

---

If you need to maintain the metadata you should be writing the reading and writing the file using data steps not proc import/export. Export is less problematic. 

But you're also involving Excel which is using it's own "proc import" to read the file.  

You're best bet is to read it in using a data step and write it using a data step, which can be automated in SAS. 

When you mention localization, is that to EU once, or do you need the ability to switch locales depending on the users?

---

@akhq wrote:

I am losing my mind over proc import... I would need to export a sas dataset into a CSV (or xls/x doesn't matter), then I would modify the file and import it back to sas. The issue is that if I add a row with new values then everything looks ok in excel, but proc import fails to import rows or even columns without any error/warning... I have tried to convert all the date variables and numeric variables into a format of 'value so that excel would consider them character, however what happens is that if I add new values then the ' will be disappearing automatically and the raw csv will also be saved without the '. I hacked this so that when filling in excel I then use '' to get single ' into the csv, and that works, however the import still fails. I have attached an testfile (last row is added after the export) and a code I have used is the following:

proc import file="&path./testdata_tomod.csv" dbms=csv replace out=import_testing; delimiter=";";run;

Then I also know that I could read the CSV using infile, but the issue is that I need to retain the original metadata for the table, and those are all lost in the process. And for example I could have in original data a value of "0000005151" CHAR and then proc import reads it in as 51515 NUM and I have no metadata in the original table to convert that back to the as stated value with correct format of zX. . 

So

a) what the heck is happening there?

b) is there any way to get dates and localisation issues handled without doing extensive manipulation and back and forth labelling etc etc.? (In EU localisation to excel uses "," as decimal delimiter instead of ".")

---

[Tom]

So you made a TEXT file and then allowed users to modify it with EXCEL?

That will generally cause all kinds of strange changes to the file.  If the users accidentally allow EXCEL to AUTOMATCIALLY convert the CSV file into a spreadsheet it will many strange things.  A common one is the automatic conversion of fixed length digit strings with leading zeros into numbers (something PROC IMPORT will also do).  Another EXCEL specific oddity is the conversion of codes with digits and hyphens into DATE values.  You can avoid most of this by always using the file import features of EXCEL (they move where this is found around from release to release but currently I found it under the menu path DATA -> Import from Text/CSV).

Similarly when reading a delimited file into SAS you should avoid using PROC IMPORT other than for exploratory purposes.  Write an actual data step that will define the variables to be created and attach appropriate formats and labels.

But your particular file has a different strange thing. You appear to have prefixed some cell values with a single quote.  

AIR;Region;id2;testdate;id;id3;date;money
112;ALL;2;'01/01/1949;1;01-Jan-24;'01/01/1949;'1000,346
118;ALL;3;'01/02/1949;1;01-Jan-24;'01/02/1949;'1000,346
132;ALL;4;'01/03/1949;3;01-Jan-24;'01/03/1949;'1000,346
129;ALL;5;'01/04/1949;4;01-Jan-24;'01/04/1949;'1000,346

I assume that is to try and force Excel to treat the values as strings?

Note that SAS will treat strings quoted with single quotes the same way it does strings quoted with double quotes when reading a delimited text file.  That could cause those leading single quotes to be paired with a later value in the same observation that terminates with a trailing single quote to become one large cell value. You are are lucky with this file since none of your other values appear to end with single quotes.  So instead SAS will just include the single quotes into the values of TESTDATE, DATE and MONEY.

Note that PROC IMPORT will assume that any column that has its value quoted on every  row should be defined as character.   So if you make the file like below instead then TESTDATE and DATE will be strings even though the values look like date strings.

AIR;Region;id2;testdate;id;id3;date;money
112;ALL;2;"01/01/1949";1;01-Jan-24;"01/01/1949";"1000,346"
118;ALL;3;"01/02/1949";1;01-Jan-24;"01/02/1949";"1000,346"
132;ALL;4;"01/03/1949";3;01-Jan-24;"01/03/1949";"1000,346"
129;ALL;5;"01/04/1949";4;01-Jan-24;"01/04/1949";"1000,346"

I am not sure if there is way to force EXCEL to write such a file.

[ballardw]

You say :"but the issue is that I need to retain the original metadata for the table". Please describe what you mean by original metadata. I ask because typically "metadata" describes things not in the values of variables such as time of data collection, instruments used, source of the file (organization) and other "stuff" that describes the data. As such, there is no way I would expect Proc Import to retain any metadata.