@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 ".")
... View more