Splitting a Table by Column Contents?

[paulrockliffe]

Thanks, I'm able to get that to work, but I've hit a problem; the code is trying to write the .csv files to outpath as if it is sat on the SAS Server under SASCompute.  

I don't have access to any of the file structure under SASCompute and need to export to a local drive, eg C:\ SASResults\ThisProject or similar.

I'm going to have a Google around, but do you have a quick fix?

Thanks again!

Edit; it's exactly this issue here: 

https://communities.sas.com/t5/SAS-Procedures/Unable-to-export-data-to-local-folders-PROC-EXPORT-in-...

[Kurt_Bremser]

If you want to write from SAS code to any location, that location must be "visible" to the SAS process.

So you can either write to a local directory on the SAS server, or a remote directory that is mounted on the SAS server.

If you want the files written from SAS code (not files created by Enterprise Guide after EG has donwloaded them internally from the SAS server) to be immediately accessible from your desktop, you have several options:

- have a share set up on the SAS server, and mount that on your desktop

- have a common share mounted on both the SAS server and the desktop

- have a share from your desktop mounted on the SAS server

- have some kind of FTP/SFTP server running on your desktop, and use filename FTP/SFTP in the SAS program

You can also make it two steps, storing to a location on the SAS server where you have write access, and then use a file transfer client (or EG itself) to download to your desktop. Since a workspace server requires the presence of a home directory (which is visible to you under Files in the Server List), you can store the files there for retrieval. The file/pathname syntax depends on the operating system of the SAS server.

[paulrockliffe]

Thanks, that's roughly what I'd gleaned from the other topics on the issue.

The problem I'm likely to have is that getting any change made to the server access will be pretty much impossible and any solution that involves too much messing around to get the results onto a normal Windows networked structure will defeat the purpose of what I'm trying to achieve.  I'll contact my Admin and see what they suggest, but it's unlikely to be a solution.

 At the moment I would export one file as a step in the process, to a Windows network folder away from the SAS environment, then use an Excel VBA code to split it.  I want SAS to do this bit for me so that one click will update all of the resulting datasets.  The results are used as dataconnections for various user tools that we develop, but we develop and don't want to be spending resource updating data where it can be done automatically.

I guess what I'm after is the code behind the 'Export {table} as a Step in Project wizard in in EG, but I've not been able to get to that.

If I run the code, then manualy add the Export step that would still save time, but then re-running the code as the data updates will remove the Export steps wouldn't it?   Is it possible to add the Export Step to each table as it's created by using code perhaps?

[Kurt_Bremser]

When you are after automating the process, your ultimate goal should be a program that can be run in batch mode and that does as much as you can out of the code. Batch programs can easily be automated from the cron daemon or from any scheduling software.

So if you need to have files moved somewhere, consider using sftp from the server to the target location. Any useful server should have a SSH server process running.

Try creating filerefs with filename sftp (or ftp, if security is not important and a ftp server is available) for the csv files, and write the export to that.

[paulrockliffe]

Thanks, I'm pretty sure I won't have access to any of those things, our environment is heavily controlled.  I'll contact my Admin and see what they suggest.

Thanks

Paul.

[Kurt_Bremser]

SSH is such a basic tool that it should be present on any server that deserves to be called a server. Really.

So it's basically a matter of knowing how to present the technical details (credentials, hostname, ports) in a filename statement.

If someone obstructs by stating security reasons, you can safely bet they're just too lazy to really manage security on the servers.

I specifically invite all my SAS users to make use of the full power of the server, and I've never encountered a problem that couldn't be dealt with by restoring a backup of a user's files. They can't do damage to anything else.

[paulrockliffe]

I tested re-running the Code Node, it appears to udate the tables that are already there, so it doesn't delete the tables already created and therefor retains the Exort Ste that's been added.

This will be sufficient for most applications, as it's not a huge job to add 8 to 10 export steps at the end of the project.  

It causes problems for some projects where rather than breaking the tables down by Sector, Region or Office, the table needs to be broken down by the Entities that sit within each Office as that would involve manually adding 2,000 Export Steps, which would take ages!

But at least I've got to a point where this is useful.

If there was a way to automate that addition of the Export Steps that would be brilliant, but I rather suspect that'll be impossible?

I've sent an email off to a couple of contacts that have more knowledge of our setup than me and may be able to advise on a solution that lets Proc Export work for me.

Thanks again!

[paulrockliffe]

Just to follow up on this, I spoke to admin and it's basically not an option to get write access on the server.  I've raised the issue so that it may be sorted when we upgrade to the latest versions later this year, but who knows.

One final question; when the tables are created I'd like to add a piece of free-text to the table names, which will be a project identifier.  I figured that would be dead easy and added a new parameter, but I can't work out how to add the parameter to the code to change the table names?

Thanks!

[Kurt_Bremser]

How about

%let project=PR32;
/* when selecting this, make sure that valid SAS names will result! */

%let outpath=/somewhere/subdir/;

data _null_;
set lookup;
call execute("proc export data=&outlib..&project._" !! trim(vname) !! " outfile='&outpath..&project._" !! trim(vname) !! ".csv' dbms=csv; run;");
run;

Do similar when creating the tables.