When you export a dataset to make corrections and then import it, do you use the same file name?

[gtucke1]

I'm working with a data set and need to export it to make some corrections. 

I export the data.

Proc export data = XYZ.data_trim2
Outfile = 'C:\Users\gt\Documents\final\SAS\trim208292022.xlsx'
DBMS = XLSX Replace;
Run;

Make changes. Then import the data. Should the import statement have the same file name as the one used for the export statement?

Proc import datafile = 'C:\Users\gt\Documents\SAS\trim208292022updated.xlsx'
DBMS = XLSX Replace
OUT = work.original_correctedcprel;
RUN;

[SASKiwi]

Yes, but you should add a SHEET statement to tell SAS what Excel tab to export to and import from:

Proc export data = XYZ.data_trim2
Outfile = 'C:\Users\gt\Documents\final\SAS\trim208292022.xlsx'
DBMS = XLSX Replace;
sheet = "data_trim2";
Run;

Proc import datafile = 'C:\Users\gt\Documents\SAS\trim208292022updated.xlsx'
DBMS = XLSX Replace
OUT = work.original_correctedcprel;
sheet = "data_trim2";
RUN;

[Tom]

I would not recommend using EXCEL to modify data.  It can mess up how the variables are defined.

If you output to a CSV file then you can read it back in with a data step so that you can control how the variables are defined.  If fact you could use the old dataset as the model for how to define the variables when you read back in the CSV file.

proc export data = XYZ.data_trim2 dbms=csv replace
  file = 'C:\Users\gt\Documents\final\SAS\trim208292022.txt'
;
run;

data corrected;
  if 0 then set xyz.data_trim2;
  infile 'C:\Users\gt\Documents\SAS\trim208292022updated.txt' dsd truncover firstobs=2;
  input (_all_) (+0);
run;

PS: NEVER let EXCEL automatically open a CSV file. It will convert things like characters strings with hyphens in them in dates and other goofy things.  You could write to an XLSX file and then instruct the person editing the file to export it as a CSV file for re-loading into SAS.

[ballardw]

---

@Tom wrote:

I would not recommend using EXCEL to modify data.  It can mess up how the variables are defined.

If you output to a CSV file then you can read it back in with a data step so that you can control how the variables are defined.  If fact you could use the old dataset as the model for how to define the variables when you read back in the CSV file.

proc export data = XYZ.data_trim2 dbms=csv replace
  file = 'C:\Users\gt\Documents\final\SAS\trim208292022.txt'
;
run;

data corrected;
  if 0 then set xyz.data_trim2;
  infile 'C:\Users\gt\Documents\SAS\trim208292022updated.txt' dsd truncover firstobs=2;
  input (_all_) (+0);
run;

PS: NEVER let EXCEL automatically open a CSV file. It will convert things like characters strings with hyphens in them in dates and other goofy things.  You could write to an XLSX file and then instruct the person editing the file to export it as a CSV file for re-loading into SAS.

---

A problem with the XLSX is still manually entered edits are subject to how Excel will "guess" what you meant a value to be. Possibly avoidable if the column format is set to something other than General but values entered by people are still subject to Excel interpretation if the entered value is dissimilar to the column format. There really isn't anything much more frustrating than entering a part number of such as 5-3 and find you that you now have a date value.

[gtucke1]

Does it make sense to export the dataset as a 7bdat?