02-13-2017 09:03 AM
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?
02-13-2017 09:11 AM
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.
02-13-2017 09:12 AM - edited 02-13-2017 09:13 AM
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.
02-13-2017 01:44 PM
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.
02-14-2017 03:20 AM
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.
02-14-2017 05:08 AM
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?
02-14-2017 05:14 AM
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.
02-14-2017 05:34 AM
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?
02-14-2017 06:04 AM
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?
02-14-2017 06:45 AM
$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.
02-14-2017 06:59 AM
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.
02-14-2017 09:18 PM
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.
02-15-2017 12:22 AM
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.
02-15-2017 03:54 AM
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.