PROC SQL Export to Excel

[paulrockliffe]

I'm building a data set in Enterprise Guide 5.1, the contents aren't important, but each row of data has a variable attached to it to specify the Region and the Sector that the row relates to.  When I get to the end of my process I have a single table that exports to Excel as a single file.

At some point the dataset is to be broken down by both Region and by Sector, ideally I'd do that in Enterprise Guide and with roughly 10 Regions and 10 Sectors it's not too difficult to usef Filter-Query to build the 20 datasets and export them.

However, doing that manually doesn't seem very efficient and wouldn't be practical if there were 100 Regions, as there could be for other work I'm involved in.  There's also an issue if a new Region or Sector appears in the data in the future as a) I wouldn't know until someone spotted the mistake and b) it would need a manual intervention to fix the problem.  There must be a better way surely?

I basically want to run the export (in a Code Node?) so that it does something along the lines of:

For each Region, filter by Region and export, then next Region.

I've not used Code Nodes very much and I don't know if that's the best approach, but would appreciate some pointers before I head off down various rabbit warrens and blind alleys?

Thanks!

[Kurt_Bremser]

Learning to write SAS code and use the code node is the jump from a button-pusher to a SAS user.

EG is fine as a helper for simple things and getting initial code, but in-depth processing requires working with code. Always remember that EG is nothing but a code generator, and all SAS work is done via code, even though you may not see it.

That said, I'd use the following approach:

Get the code for extracting a region and exporting, test it.

Identify how and where region is used, replace it with a macro variable and test again

Once it works, wrap it into a macro definition and use the region macro variable as macro parameter.

Now do the following:

proc sort data=have (keep=region) out=regions nodupkey;
by region;
run;

/* Alternatively you can do a proc sql select distinct region */

data _null_;
set regions;
call execute('%macroname('!!trim(region)!!');');
run;

Depending on the type of region, the call execute might need to be phrased differently.

[RW9]

Well, not used EG for ages, but from a coding point of view:

proc sort data=have out=loop nodupkey;
  by region;
run;

libname myxl excel "<pathtofile>\<filename>.xlsx";

data _null_;
  set loop;
  call execute('data myxl.'||strip(region)||'; set have (where=region="'||strip(region)||'")); run;');
run;

libname myxl clear;

This assumes region has valid names, and assume you have access to the excel engine etc.  All assumptions I have had to make as you have not presented any system information, code, etc.  So its all guess work.

[Reeza]

Are you trying to create multiple excel file, one for each region/sector or a single file with multiple tabs for each region/sector.

The second is easier, but the first is easy as well 🙂

The first option requires a macro and the second can use ODS EXCEL or ODS TAGSETS.EXCELXP. 

For the first see the sample macros in the documentation, there's one for CSV that you can probably adapt for Excel files.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[paulrockliffe]

Thanks, I'll have a look through those options and see how I get on.

I can cope with either one spreadsheet or several, I haven't decided which is best as I wasn't sure which I'd be able to get working.

Ideally I would export into an Excel Template file, but I can use a Data Connection between a template and the EG output file(s) to achieve the same thing.

[paulrockliffe]

I'm getting this error using ods tagsets.excelxp :

ERROR: Insufficient authorization to access /opt/app/sas9.2/.......

I presume this means it's trying to write the file relative to the server address, which I'm not allowed to do.  Is this a terminal problem or is there a way to tell it to reference relative to my local machine?

Thanks!

[Kurt_Bremser]

Your server has it's own filesystem, and SAS can access only that. Unless a share from your desktop is mounted on the server (highly unlikely), you can only create files on the server. The easiest location is your home directory, so use $HOME/filename in the file (or filename) statement.

[paulrockliffe]

Thanks, I've got rid of that error now.  I just need to fix a few other errors to get it to write the file, but inevitably I'll want to know how to get the file from my $Home directory to a local folder?

[paulrockliffe]

Actually, I can't even find my $HOME folder!  I managed to get the process to work and I can access the file that's produced and open it in Excel to then save from the Process Flow, but I don't know how to delete the file and I'd prefer not to just create files in an invisible location until *someone* tells me off!

If I right-click and Delete the file in the Process Flow, will it delete it in the $HOME folder?

[Kurt_Bremser]

$HOME is a UNIX environment variable that contains the path to your home directory (usually /home/username). In the default workspace server configuration, the Files section in the server list (under SASApp or whatever your workspace server is called) will point there.

So you should find your created files in Server List/SASApp/Files

There exists a custom EG task for file transfer between server and client, created by @ChrisHemedinger : http://blogs.sas.com/content/sasdummy/2012/12/06/copy-files-in-sas-eg/

Or you can use a file transfer utility (like WinSCP) to connect to the server's secure shell port.

[paulrockliffe]

OK, I thought it would be there, but I can't access it, I get a couple of folders that relate to systems that run on the SAS Server, but nothing else.

I also can't install the Copy Files thing here as I don't have that sort of access.  

I am able to open the created file, it does download and open in Excel, but I'm not sure I can use that if there's no way for me to cleanup the $HOME folder afterwards.

[CaseySmith]

Just wanted to point out that starting in EG 7.13, the Copy Files task (previously a custom task, written by Chris Hemedinger) is built into EG.  It can be accessed from the Tasks->Data->Copy Files menu.

[Kurt_Bremser]

Check with your SAS administrator which file navigation starting point is set for the workspace server. We can then proceed from there. If he/she changed it from the default (user's home directory), ask why this was done.

[paulrockliffe]

I'll ask some questions and see if there's someone who would know who to speak to.  Problem is our SAS setup is managed by a third party and I'm a very small cog in a massive wheel, it is what it is and If I can't navigate to the folder there's no chance that can be changed.  Changes are on hold anyway as we're moving to a new setup and the latest version of SAS later in the year.

I owuld guess that it is how it is for security reasons, but someone hasn't considred that the directory is writeable even if you can't see it.