DATA Step, Macro, Functions and more

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

Reply
Valued Guide
Posts: 505

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

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;
PROC Star
Posts: 1,567

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

A better place to publish this might be the library?

 

Ask a Question
Discussion stats
  • 1 reply
  • 112 views
  • 1 like
  • 2 in conversation