send sas program output to csv file on server

[PriitL]

Hi!

I'm doing my first steps to automate some basic tasks with SAS and trying to migrate output with linux utilities. For that I need an output from SAS script to be written to file (csv) executed from linux command line.

At the moment I'd like to get a user list from metadata regularly and output it as a csv file on server. I found some scripts from web to get a user list and they work fine with EG:

https://seleritysas.com/2017/08/29/data-step-view-of-users-in-sas-metadata/

https://communities.sas.com/t5/Administration-and-Deployment/Querying-the-metadata-for-a-list-of-use...

The script, when run with EG, displays all the users from metadata.

I have managed to get these scripts to work also from command line executed by:

<sashome>/SASFoundation/9.4/sas -sysin /<path-to-script>/<script-name>.sas -log /<path-to-log-files>/<logfile>

The script contains logon info to access metadata and there's no error in logfile.

Unfortunately I can't get an output from these scripts, probably they are just run but the output is not shown on the screen.

Please advice.

It would be great if the script sends the output to stdout and I could send it to file. Also, I could use a library (BASE type) named "sasmacr" which is assigned to SASApp server and contains real linux folder path to write to. In that case I get sas7bcat file and not csv, still.

Sorry if my problem is badly expressed.

Thanks!

PL

[Amir]

Hi,

If I have understood you correctly, you could try using a SAS supplied macro %ds2csv which allows you to specify a SAS data set and a file name and location to where you want it to be output as a CSV.

This could be added to the end of your script, as required.

Thanks & kind regards,

Amir.

[PriitL]

Thank You for the reply.

I took the script posted on https://communities.sas.com/t5/Administration-and-Deployment/Querying-the-metadata-for-a-list-of-use...

and added a line to the end of script so it looks like:

...
  break after name / skip; 
run; 

%ds2csv (openmode=APPEND, runmode=b, csvfile=/opt/sas/data/fail.csv);

"Append" was just for testing. I'd prefer that each output goes to separate "yyyy-mm-dd.csv" format file but I'll deal with it later.

I didn't specify "data" parameter as the documentation noted "if you omit the data set name, DS2CSV attempts to use the most recently created SAS data set". I'm not sure, maybe it already "forgot" the latest dataset after the last "run;" and the %ds2csv should be a bit earlier in the script?

Because it complains:

95         %ds2csv (openmode=APPEND, runmode=b, csvfile=/opt/sas/data/fail.csv);
ERROR: The connection to the X display server could not be made. Verify that the X display name is correct, and that you have 
       access authorization. See the online Help for more information about connecting to an X display server.

ERROR: Device does not support full-screen.


ERROR: Errors printed on page 3.

Any hints?

Thanks!

PL

[PriitL]

When I added the similar line to code in EG, at first if complained about bad path for "retail.csv" file. It tried to write the file straight to the server and was unable due to permissions. So i added:

%ds2csv (runmode=b, csvfile=<path-i-have-write-permission>/retail.csv);

And it worked.

A little step forward.

[Tom · Accepted solution]

%DS2CSV is a little bit of overkill for just writing a CSV file. It has lots of extra stuff to deal with SAS/Intrnet outputs since that is where it comes from.  There probably is an option to prevent it from trying to open a display.   Or you could possibly add the -noterminal option to your command that calls SAS.

To just write a CSV without a header line (why do you need the headerline when you know what variables you are writing) just use a simple data step like this:

data _null_;
  set mydataset ;
  file 'myfilename.csv' dsd ;
  put (_all_) (+0);
run;

If the lines might get longer then 32k bytes then add a LRECL= option to the FILE statement.

To include the current date into the name of the file you want to use a little bit of macro code. Remember to use double quote character instead of single quote characters around the string literal so the macro processor will resolve the macro code.

data _null_;
  set mydataset ;
  file "myfilename_%sysfunc(date(),yymmddn8.).csv" dsd ;
  put (_all_) (+0);
run;

[quickbluefish]

I'm not completely sure I understand what you want.  Are you trying to get a list of users that are currently logged into the Linux server?  Or a list of people currently using SAS?  And then you're trying to either export that list to a csv file or display it on the screen?  

When you run that macro from the command line using the -sysin option, are you saying that you cannot find the resulting .lst or .log file (in the directory where you launched the program)?

If this is a SAS Grid system, then you have to issue a separate command to retrieve the .log / .lst after the program completes (or you can use the -gridwaitresults to do this automatically.  However, if you are not using SAS Grid, this wouldn't apply.  

If you just want to capture users, you could use the PIPE command with FILENAME - something like this:

** submit the Linux command "who" to the OS ;
filename users PIPE "who";

* capture the above into a SAS dataset ;
data users;
infile users;
length userlist $250;
input userlist;
run;

** output to csv ;
proc export data=users dbms=csv replace
    outfile="/path/to/file/users.csv";
run;

If you need to display this on the screen (meaning, the terminal window), I think there's a way to redirect output to the terminal, but I don't remember offhand how to do this.  

However, if I can give you a piece of advice, any sort of interactions you want to do with the operating system are going to be SO MUCH easier if you skip SAS entirely and write a shell script instead.  Even Python or Perl (which are almost certainly already on your Linux setup) are going to be easier to do this with than SAS.  But really, save yourself the effort and write a shell script.  LLMs like ChatGPT are very helpful if you don't know shell / bash script syntax.  

You could even launch the shell script with SAS using the X command (though if you're using Grid, anything that the script is trying to display on the screen will not show up on YOUR screen because Grid runs things in a separate place).  

[PriitL]

Thanks for the reply.

The main goal is to get users list from metadata daily and store the list. It means, all users who currently have an account in Metadata to access SAS.

Output format is not so critical as I could manipulate with it later from Bash. So it doesn't matter if the script shows the list on screen and I send stdout to file or writes it directly to file whether it is csv or just text.

The command

<sashome>/SASFoundation/9.4/sas -sysin /<path-to-script>/<script-name>.sas -log /<path-to-log-files>/<logfile>

generates a log file and result was pasted on previous posts.

This is not a Grid environment. We have 3 separate server (mid/meta, SASApp and SASApp_VA) but I think it's irrelevant as the users list query is done to metadata and I assume it writes all the output to the server where the script was executed.

Thanks!

[quickbluefish]

I see - I completely misunderstood what you meant by a user list. Is it possible to navigate through the Linux filesystem to a list of users somewhere in the SASFoundation folders?

[PriitL]

I'm not sure if I understood, please specify.

I can surf between directories on server but I'm not sure what to look for according to Your suggestion.

I just need a list with usernames from Metadata and to modify the sctipt in a way it displays output to stdout (for sending it to file) or writes the output to file.

[quickbluefish]

Well, if the code you were using is successfully producing a SAS dataset that contains the user list from metadata and all you need to do is export that to a CSV, then you should be able to use one of the methods suggested above.  I usually use PROC EXPORT, but there are plenty of other ways, as Tom and others have suggested.  To export a dataset called "users" to a csv file called "user_list.csv", you can just do this:

proc export data=users dbms=csv replace
    outfile="/path/to/your/directory/user_list.csv";
run;

About navigating to a metadata user list in the Linux filesystem, I just looked that up, and it turns out that you cannot access such a list through the filesystem - you have to do it within SAS (as you're doing with that code you found).  If you just want to find where SAS Foundation is located, you could start by typing "which sas" at the command line, but again, it sounds like the user list is not available there.  Good luck.

[Tom]

If you want to write to STDOUT and you are running SAS on Unix then you will already have a fileref named STDOUT you can use.

Try it with PROC EXPORT:

proc export data=users file=stdout dbms=csv;
run;

If PROC EXPORT does not work right (it does have some strange quirks) then just run your own data step to write the CSV lines.

data _null_;
  file stdout dsd ;
  set users;
  put (_all_) (+0);
run;