Code to interface with Perl, Python, R32, R64, MS_R64 and WPS rom SAS

rogerjdeangelis — Fri, 10 Feb 2017 15:47:11 GMT
Code to interface with Perl, Python, R32, R64, MS_r64 and WPS rom SAS

You can interface with the various R distributions using IML

%utl_submit_pl64     PERL
%utl_submit_py64     PYTHON
%utl_submit_r64      R 64
%utl_submit_r32      R 32
%utl_submit_msr64    Microsoft muti-threaded mathpack R
%utl_submit_wps64    WPS 64


*____           _
|  _ \ ___ _ __| |
| |_) / _ \ '__| |
|  __/  __/ |  | |
|_|   \___|_|  |_|

;

*Running a perl program (macro below);

* create a spreadsheet;
libname xel "d:/xls/simple.xlsx";
data xel.simple;
  set sashelp.class;
run;quit;
libname xel clear;

* print cells A1 and A2 from an excel worksheet;
%utl_submit_pl64("
#!/usr/bin/perl
use strict;
use warnings;
use 5.14.0;
use Spreadsheet::Read qw(ReadData);
my $book = ReadData ('d:/xls/simple.xlsx');
say 'A1: ' . $book->[1]{A1};
say 'A2: ' . $book->[1]{A2};
");

* in output window;
A1: NAME
A2: Alfred

*____        _   _
|  _ \ _   _| |_| |__   ___  _ __
| |_) | | | | __| '_ \ / _ \| '_ \
|  __/| |_| | |_| | | | (_) | | | |
|_|    \__, |\__|_| |_|\___/|_| |_|
       |___/
;

options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.class;
  set sashelp.class;
run;quit;

%utl_submit_py64('
import numpy as np;
import pandas as pd;
from sas7bdat import SAS7BDAT;
with SAS7BDAT("d:/sd1/class.sas7bdat") as m:;
.   mdata = m.to_data_frame();
print(mdata);
');

       NAME SEX   AGE  HEIGHT  WEIGHT
0    Alfred   M  14.0    69.0   112.5
1     Alice   F  13.0    56.5    84.0
2   Barbara   F  13.0    65.3    98.0
3     Carol   F  14.0    62.8   102.5
4     Henry   M  14.0    63.5   102.5
5     James   M  12.0    57.3    83.0
6      Jane   F  12.0    59.8    84.5
7     Janet   F  15.0    62.5   112.5
8   Jeffrey   M  13.0    62.5    84.0
9      John   M  12.0    59.0    99.5
10    Joyce   F  11.0    51.3    50.5
11     Judy   F  14.0    64.3    90.0
12   Louise   F  12.0    56.3    77.0
13     Mary   F  15.0    66.5   112.0
14   Philip   M  16.0    72.0   150.0
15   Robert   M  12.0    64.8   128.0
16   Ronald   M  15.0    67.0   133.0
17   Thomas   M  11.0    57.5    85.0
18  William   M  15.0    66.5   112.0


*____
|  _ \
| |_) |
|  _ <
|_| \_\

;

%utl_submit_r64('
library(haven);
class<-read_sas("d:/sd1/class.sas7bdat");
class;
');

# A tibble: 19 × 5
      NAME   SEX   AGE HEIGHT WEIGHT
     <chr> <chr> <dbl>  <dbl>  <dbl>
1   Alfred     M    14   69.0  112.5
2    Alice     F    13   56.5   84.0
3  Barbara     F    13   65.3   98.0
4    Carol     F    14   62.8  102.5
5    Henry     M    14   63.5  102.5
6    James     M    12   57.3   83.0
7     Jane     F    12   59.8   84.5
8    Janet     F    15   62.5  112.5
9  Jeffrey     M    13   62.5   84.0
10    John     M    12   59.0   99.5
11   Joyce     F    11   51.3   50.5
12    Judy     F    14   64.3   90.0
13  Louise     F    12   56.3   77.0
14    Mary     F    15   66.5  112.0
15  Philip     M    16   72.0  150.0
16  Robert     M    12   64.8  128.0
17  Ronald     M    15   67.0  133.0
18  Thomas     M    11   57.5   85.0
19 William     M    15   66.5  112.0


*_        ______  ____     __   _  _
\ \      / /  _ \/ ___|   / /_ | || |
 \ \ /\ / /| |_) \___ \  | '_ \| || |_
  \ V  V / |  __/ ___) | | (_) |__   _|
   \_/\_/  |_|   |____/   \___/   |_|

;

* WPS express does not limit the number of rows returned to SAS;
* if you have one data set in work WOS will return a SAS dataset not a WPD dataset;
%utl_submit_wps64('
libname sd1 "d:/sd1";
options set=R_HOME "C:/Program Files/R/R-3.3.2";
libname wrk "%sysfunc(pathname(work))";
proc r;
submit;
source("C:/Program Files/R/R-3.3.2/etc/Rprofile.site", echo=T);
library(haven);
class<-read_sas("d:/sd1/class.sas7bdat");
class<-class[class$SEX=="M",];
class;
endsubmit;
import r=class  data=wrk.classwps;
run;quit;
');

INSIDE R
# A tibble: 10 × 5
      NAME   SEX   AGE HEIGHT WEIGHT
     <chr> <chr> <dbl>  <dbl>  <dbl>
1   Alfred     M    14   69.0  112.5
2    Henry     M    14   63.5  102.5
3    James     M    12   57.3   83.0
4  Jeffrey     M    13   62.5   84.0
5     John     M    12   59.0   99.5
6   Philip     M    16   72.0  150.0
7   Robert     M    12   64.8  128.0
8   Ronald     M    15   67.0  133.0
9   Thomas     M    11   57.5   85.0
10 William     M    15   66.5  112.0

proc print data=classwps;
run;quit;

Returned to SAS;

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Henry       M      14     63.5      102.5
  3    James       M      12     57.3       83.0
  4    Jeffrey     M      13     62.5       84.0
  5    John        M      12     59.0       99.5
  6    Philip      M      16     72.0      150.0
  7    Robert      M      12     64.8      128.0
  8    Ronald      M      15     67.0      133.0
  9    Thomas      M      11     57.5       85.0
 10    William     M      15     66.5      112.0





*____  _____ ____  _
|  _ \| ____|  _ \| |
| |_) |  _| | |_) | |
|  __/| |___|  _ <| |___
|_|   |_____|_| \_\_____|

;

* create a spreadsheet;
libname xel "d:/xls/simple.xlsx";
data xel.simple;
  set sashelp.class;
run;quit;
libname xel clear;

* read the sheet in PERL and output to SAS output window;
%macro utl_submit_pl64(pgm)/des="Semi colon separated set of py commands";
  * write the program to a temporary file;
  filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
  data _null_;
    length pgm  $32755 cmd $255;
    file py_pgm ;
    pgm=&pgm;
    semi=countc(pgm,';');
      do idx=1 to semi;
        cmd=cats(scan(pgm,idx,';'),';');
        put cmd $char96.;
        putlog cmd $char96.;
      end;
  run;
  run;quit;
  %let _loc=%sysfunc(pathname(py_pgm));
  %put &_loc;
  filename rut  pipe "perl -w &_loc > __log.txt";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;quit;
  filename rut clear;
  filename py_pgm clear;
  data _null_;
    infile "__log.txt";
    input;
    put _infile_;
  run;quit;
%mend utl_submit_pl64;


%utl_submit_pl64("
#!/usr/bin/perl
use strict;
use warnings;
use 5.14.0;
use Spreadsheet::Read qw(ReadData);
my $book = ReadData ('d:/xls/simple.xlsx');
say 'A1: ' . $book->[1]{A1};
say 'A2: ' . $book->[1]{A2};
");

A1: NAME
A2: Alfred


*____        _   _
|  _ \ _   _| |_| |__   ___  _ __
| |_) | | | | __| '_ \ / _ \| '_ \
|  __/| |_| | |_| | | | (_) | | | |
|_|    \__, |\__|_| |_|\___/|_| |_|
       |___/
;

%macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands";
  * write the program to a temporary file;
  filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
  data _null_;
    length pgm  $32755 cmd $255;
    file py_pgm ;
    pgm=&pgm;
    semi=countc(pgm,';');
      do idx=1 to semi;
        cmd=cats(scan(pgm,idx,';'));
        if cmd=:'.' then cmd=substr(cmd,2);
        put cmd $char96.;
        putlog cmd $char96.;
      end;
  run;

  run;quit;
  %let _loc=%sysfunc(pathname(py_pgm));
  %put &_loc;
  filename rut pipe  "C:\Python_27_64bit/python.exe &_loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;
  filename rut clear;
  filename py_pgm clear;
%mend utl_submit_py64;

*____   __   _  _
|  _ \ / /_ | || |
| |_) | '_ \| || |_
|  _ <| (_) |__   _|
|_| \_\\___/   |_|

;

%macro utl_submit_R64(pgmx)/des="Semi colon separated set of R commands";
  * write the program to a temporary file;
  filename r_pgm temp lrecl=32766 recfm=v;
  data _null_;
    file r_pgm;
    pgm=&pgmx;
    put pgm;
    putlog pgm;
  run;
  %let __loc=%sysfunc(pathname(r_pgm));
  * pipe file through R;
  filename rut pipe "c:\Progra~1\R\R-3.3.2\bin\x64\R.exe --vanilla --quiet --no-save < &__loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
    putlog _infile_;
  run;
  filename rut clear;
  filename r_pgm clear;
%mend utl_submit_r64;


*____    _________
|  _ \  |___ /___ \
| |_) |   |_ \ __) |
|  _ <   ___) / __/
|_| \_\ |____/_____|

;

%macro utl_submit_R32(pgmx)/des="Semi colon separated set of R commands";
  * write the program to a temporary file;
  filename r_pgm temp lrecl=32766 recfm=v;
  data _null_;
    file r_pgm;
    pgm=&pgmx;
    put pgm;
    putlog pgm;
  run;
  %let __loc=%sysfunc(pathname(r_pgm));
  * pipe file through R;
  filename rut pipe "c:\Progra~1\R\R-3.3.2\bin\i386\R.exe --vanilla --quiet --no-save < &__loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
    putlog _infile_;
  run;
  filename rut clear;
  filename r_pgm clear;
%mend utl_submit_r32;

*__  __ ____    ____     __   _  _
|  \/  / ___|  |  _ \   / /_ | || |
| |\/| \___ \  | |_) | | '_ \| || |_
| |  | |___) | |  _ <  | (_) |__   _|
|_|  |_|____/  |_| \_\  \___/   |_|

;

%macro utl_submit_msr64(pgmx)/des="Semi colon separated set of R commands";
  * write the program to a temporary file;
  filename r_pgm temp lrecl=32766 recfm=v;
  data _null_;
    file r_pgm;
    pgm=&pgmx;
    put pgm;
    putlog pgm;
  run;
  %let __loc=%sysfunc(pathname(r_pgm));
  * pipe file through R;
  filename rut pipe "D:\exe\Microsoft\MRO-3.3.1\bin\x64\r.exe --vanilla --quiet --no-save < &__loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
    putlog _infile_;
  run;
  filename rut clear;
  filename r_pgm clear;
%mend utl_submit_msr64;

*_        ______  ____     __   _  _
\ \      / /  _ \/ ___|   / /_ | || |
 \ \ /\ / /| |_) \___ \  | '_ \| || |_
  \ V  V / |  __/ ___) | | (_) |__   _|
   \_/\_/  |_|   |____/   \___/   |_|

;

 %macro utl_submit_wps64(pgmx,resolve=Y)/des="submiit a single quoted sas program to wps";
  * write the program to a temporary file;

  %utlfkil(%sysfunc(pathname(work))/wps_pgmtmp.wps);
  %utlfkil(%sysfunc(pathname(work))/wps_pgm.wps);


  filename wps_pgm "%sysfunc(pathname(work))/wps_pgmtmp.wps" lrecl=32756 recfm=v;
  data _null_;
    length pgm  $32756 cmd $32756;
    file wps_pgm ;
    %if %upcase(%substr(&resolve,1,1))=Y %then %do;
       pgm=resolve(&pgmx);
    %end;
    %else %do;
      pgm=&pgmx;
    %end;
    semi=countc(pgm,';');
      do idx=1 to semi;
        cmd=cats(scan(pgm,idx,';'),';');
        len=length(strip(cmd));
        put cmd $varying32756. len;
        putlog cmd $varying32756. len;
      end;
  run;
  filename wps_fin "%sysfunc(pathname(work))/wps_pgm.wps" lrecl=255 recfm=v ;
  data _null_ ;
    length textin $ 32767 textout $ 255 ;
    * file "%sysfunc(pathname(work))/wps_pgmtmp.wps" lrecl=255 recfm=v ;
    file wps_fin;
    infile "%sysfunc(pathname(work))/wps_pgmtmp.wps" lrecl=32767 truncover;
    format textin $char32767.;
    input textin $char32767.;
    if lengthn( textin ) <= 255 then put textin ;
    else do while( lengthn( textin ) > 255 ) ;
       textout = reverse( substr( textin, 1, 255 )) ;
       ndx = index( textout, ' ' ) ;
       if ndx then do ;
          textout = reverse( substr( textout, ndx + 1 )) ;
          put textout $char255. ;
          textin = substr( textin, 255 - ndx + 1 ) ;
    end ;
    else do;
      textout = substr(textin,1,255);
      put textout $char255. ;
      textin = substr(textin,255+1);
    end;
    if lengthn( textin ) le 255 then put textin $char255. ;
    end ;
  run ;
  %let _loc=%sysfunc(pathname(wps_fin));
  %let _w=%sysfunc(compbl(C:/Progra~1/worldp~1/bin/wps.exe -autoexec c:\oto\Tut_Otowps.sas -config c:\cfg\wps.cfg));
  %put &_loc;
  filename rut pipe "&_w -sysin &_loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;
  filename rut clear;
  filename wps_pgm clear;
  data _null_;
    infile "wps_pgm.lst";
    input;
    putlog _infile_;
  run;quit;
%mend utl_submit_wps64;
Re: Code to interface with Perl, Python, R32, R64, MS_R64 and WPS rom SAS

ChrisNZ — Mon, 13 Feb 2017 22:47:27 GMT
A better place to publish this might be the library?
topic Re: Code to interface with Perl, Python, R32, R64, MS_R64 and WPS rom SAS in SAS Programming

Code to interface with Perl, Python, R32, R64, MS_R64 and WPS rom SAS

Re: Code to interface with Perl, Python, R32, R64, MS_R64 and WPS rom SAS
