BookmarkSubscribeRSS Feed
patelpn
Fluorite | Level 6

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.

4 REPLIES 4
Reeza
Super User
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
Fluorite | Level 6
Thank you.
ballardw
Super User

 

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
Fluorite | Level 6
Thank you. I am also trying the same way.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 502 views
  • 6 likes
  • 3 in conversation