Importing excel file into sas

[patelpn]

I am trying to export a dataset into excel and give it out for review and importing the excel file back into sas.

I want the format and length to be same as the previous sas dataset which i exported.

I dont want to convert into csv and then import it and also i want the log to complain if the format or length is different from the previous one when i import the excel file.

Thanks.

[Reeza]

Excel doesn't enforce types or lengths and SAS doesn't have a good way of doing this unfortunately.
Doing exactly what you want here isn't going to be easy but there are likely workarounds, which you already know - CSV or other formats.

[patelpn]

Thank you.

[ballardw]

Likely the easiest tool to do the needed comparison is going to be PROC Compare. The procedure will report if variable types, formats or lengths of variables are different if they have the same name as well as reporting on variables that may appear in only one set (such as a changed name or other addition).

The following makes a modified version of a data set you should have available and runs proc compare between the original and the modified set.

data work.class;
   length sex $2 ;
   format age f3.;
   set sashelp.class;
   wtht= weight/height;
run;

proc compare base=sashelp.class
     compare=work.class;
run;

If the VALUES are not the same, such as some one sorts the Excel before you re-import it or types a different value into a few cells you will also get some details about records that don't match or values that don't match.

There are a fair number of options involved with Proc Compare but this should get you started.

Remember to reimport the data into a differently named data set than your original so that you do not replace the old version.

There really isn't much guarantee that reimported Excel spreadsheets have the same characteristics, especially if you are using Proc Import as the characteristics of variables are set by only examining about 20 rows of data when using Proc Import. I have tested the results with using PC Files server though. So good luck.

[patelpn]

Thank you. I am also trying the same way.