Copying like this (input/put) is a little clunky I think, but it works. X commands are not enabled by default from Enterprise Guide, so this is the best option remaining.

The data will be stored in named ranges set up for that purpose – but before they can be populated, they need to be cleared:

proc datasets library=wb;
delete Listing Header;
run;
quit;

Listing in this case would be a tabular dataset, and Header is a one row set which contains information like report date, refresh date, titles etc. – any information which is particular to that edition of the report.

The code to populate Listing would run much the same way. One point to note here is that sometimes the order of the columns output is important. In this case, it is possible to control the order of columns by either using a retain statement in the data step, or by using proc sql and ordering the columns in the select clause.

Once the ranges have been populated, close the file by clearing the libname:

libname wb clear;

This is important as otherwise the file will be held open by SAS.

And there you have it – the report has now been populated. Actually, there’s a further wrinkle I should mention. For some reason, the report file created often suffers from a peculiar “bloating” effect, where the file size appears to be roughly double what it should be. However, once the file is opened and saved in Excel, the size returns to normal. In a couple of cases involving large numbers of regular reports, I have written a VBA routine to open and save each one in turn…as with the copy step above, I find it a little clunky, but it can save a considerable amount of space.