Converting SAS scripts containing macros, data step into BigQuery compatible

[vigneswar17]

Hi Folks,

I have a SAS code which has sas macro comprising of data step and procedural SQLs within it. Below is the sample code,

%macro VoidRate(product_abbre);

data ndcs2_dummy;
set savedata.ndcs2_dummy;
run;

proc sql;
%connect_db(&dbuser_dummy.,libname=yes, dbmstemp=yes);
drop table &dbuser_dummy..ndcs2_dummy;

create table &dbuser_dummy..ndcs2_dummy(distribute=random bulkload=yes dbtype=(chndc='character varying(17)') bl_options="logdir '%sysfunc(pathname(work))'") as
select * from work.ndcs2_dummy;

data savedata.count_all_&product_abbre._dummy;
format chain_name $30. ichainID 3.;
set count_dummy count_ra_dummy count_kroger_dummy;
run;

proc means data=savedata.count_all_&product_abbre._dummy n mean ;
var first_void ;
output out=void_dummy mean=first_void;
format first_void percent8.1;
run;

%mend VoidRate;

%macro mcr_encodedups_perm(dwdb=xx,dsnin=dummy_encode,dsnout=xx,debug_flg=N,verbose_flg=N);

%local begin_timestamp;
*** Set beginning timestamp ***;
%let begin_timestamp=%sysfunc(datetime());
%let verbose_flg=%upcase(%substr(&verbose_flg,1,1));
%let debug_flg=%upcase(%substr(&debug_flg,1,1));

%if &verbose_flg=N %then %do; %let debug_flg=N; %end;

%let rc_data=%sysfunc(exist(admin_errout_dups));
%if %eval(&rc_data=0) %then
%do;
proc datasets library=work nolist;
delete admin_errout_dups;
quit;
%end;

%let rc_data=%sysfunc(exist(&dwdb..&dsnin));*User must have dsnin data set;
%if %eval(&rc_data=0) %then
%do;
%put -------------------------------------------------------------------------------;
%put Error - DSNIN DataSet &dsnin does not exist;
%put Processing Aborted ;
%put -------------------------------------------------------------------------------;
%goto exit;
%end;

%let nzlogs='%sysfunc(pathname(work))';

*** Check to be sure 3 key variables are on dsnin data set ***;
%let dsid=%sysfunc(open(&dwdb..&dsnin,i));
%let pat_key=%sysfunc(varnum(&dsid,pat_key));
%let tran_nbr=%sysfunc(varnum(&dsid,tran_nbr));
%let rc=%sysfunc(close(&dsid));
%if %eval(&dsid=0) %then
%do;
%put -------------------------------------------------------------------------------;
%put Error - DSNIN DataSet &dwdb..&dsnin does not exist;
%put Processing Aborted ;
%put -------------------------------------------------------------------------------;
%goto exit;
%end;
%if %eval(&pat_key=0) or %eval(&tran_nbr=0) %then
%do;
%put -------------------------------------------------------------------------------;
%put Error - Key Data Element does NOT exist in &dsnin. data set;
%if %eval(&pat_key=0) %then %put %str(Variable "pat_key" does not exist);
%if %eval(&tran_nbr=0) %then %put %str(Variable "tran_nbr" does not exist);
%put Processing Aborted ;
%put -------------------------------------------------------------------------------;
%goto exit;
%end;

proc sql;
/* %connect_db(pohus2,libname=yes);*/
%connect_db(&dwdb,auth=Adheris_Netezza_Power_Auth);

%if %sysfunc(exist(&dwdb..NDCGCN)) %then %do;
drop table &dwdb..NDCGCN;
%end;
%if %sysfunc(exist(&dwdb..ndc_keys)) %then %do;
drop table &dwdb..ndc_keys;
%end;
%if %sysfunc(exist(&dwdb..ptNdcXrf)) %then %do;
drop table &dwdb..ptNdcXrf;
%end;
%if %sysfunc(exist(&dwdb..NdcXrf)) %then %do;
drop table &dwdb..NdcXrf;
%end;
%if %sysfunc(exist(&dwdb..ptNdcXrf2)) %then %do;
drop table &dwdb..ptNdcXrf;
%end;
%if %sysfunc(exist(&dwdb..&dsnout.00)) %then %do;
proc sql; drop table &dwdb..&dsnout.00;
%end;
%if %sysfunc(exist(&dwdb..&dsnout.000)) %then %do;
proc sql; drop table &dwdb..&dsnout.000;
%end;
%if %sysfunc(exist(&dwdb..&dsnout)) %then %do;
proc sql; drop table &dwdb..&dsnout;
%end;
%if %sysfunc(exist(&dwdb..&dsnout)) %then %do;
proc sql; drop table &dwdb..&trx_sold_check;
%end;

quit;

%if &verbose_flg. = N %then %do;
proc sql;
%connect_db(&dwdb,auth=Adheris_Netezza_Power_Auth);

execute( create temp table &dsnout as
select t2.*
from &dsnin sq1
join asv_dupenc_v t2 using(pat_key,tran_nbr)
distribute on (pat_key)
) by &dwdb;
quit;
%goto exit;
%end;

%mend mcr_encodedups_perm;

I know we can convert SQL statements from SAS to BQ but I want to understand whether it is possible to convert sas macros, data step and all the functionalities in SAS compatible to BigQuery. If yes is there are any documentations, website or any cheat sheet for the same?

Any leads on this are much appreciated.