SAS, programmatically export metadata object spks

Question

In SAS, I have a folder structure that contains a large number of SAS table metadata.

To migrate from one environment to another, we need to manually create a large number of spks and push them to Git. This is problematic because it (a) takes time, (b) developers are incentivized to package a large number of metadata objects into one .spk, which creates problems down the line, when only one of those objects has to be edited and none can be reviewed.

Is there:

1. A way to programmatically export an .spk file?
2. A way to parse the folder structure and create one spk file per metadata object?

It would be great if you could point me to helpful SAS papers or point me to the correct direction.

Answer 1

There are two things to point out with your approach:

1. The same metadata item will have different checksums when exported twice, due to embedded timestamps. This will always create differences in GIT.
2. Exporting every metadata item separately will take a long time - the ExportPackage routine isn't that quick.

But anyway, of course what you are proposing can be done! I just tested the below, and it works fine. If you are not on windows you may have to adjust the path to the ExportPackage utility in the pipe (using semicolon - ; - to chain commands)..

%macro exporter(root=%sysfunc(pathname(work)) /* physical root */
    ,host=dev-meta.ACME.int /* metadata server */
    ,port=8561              /* metadata port */
    ,user=sasdemo           /* user with metadata credentials */
    ,pass=Mars123           /* password */
  );
options noquotelenmax;

data paths (keep=tree_path uri treetype);
  length tree_path $500 uri tree_uri parent_uri 
    parent_name TreeType PublicType $256;
  n=1;
  do while(metadata_getnobj("omsobj:Tree?@PublicType = 'Folder'",n,uri)>0);
  /* code for getting the metadata path */
    rc=metadata_getattr(uri,"Name",tree_path);
    rc=metadata_getattr(uri,"TreeType",TreeType);
    rc=metadata_getattr(uri,"PublicType",PublicType);
    tree_uri=uri;
    do while (metadata_getnasn(tree_uri,"ParentTree",1,parent_uri)>0);
      rc=metadata_getattr(parent_uri,"Name",parent_name);
      tree_path=strip(parent_name)||'/'||strip(tree_path);
      tree_uri=parent_uri;
    end;
    tree_path='/'||strip(tree_path);
    call symputx(cats('path',n),tree_path,'l');
    call symputx(cats('uri',n),uri,'l');
    call symputx('n',n,'l');
    output;
    n+1;
    if n>3 then leave;  /* remove this unless testing */
  end;
run;
proc sort; by tree_path;run;

/* get location of BatchExport metadata tool */
/*http://support.sas.com/documentation/cdl/en/bisag/64088
     /HTML/default/viewer.htm#a003261084.htm*/
data _null_;
   h="%sysget(SASROOT)";
   h2=substr(h,1,index(h,"SASFoundation")-2);
   call symputx("platform_object_path"
    ,cats(h2,"/SASPlatformObjectFramework/&sysver"));
run;
%put Batch tool located at: &platform_object_path;

%let connx_string= -host &host -port &port -user &user -password &pass;

%do x=1 %to &n;
  data out&x (drop=n rc);
    length uri objuri Name PublicType path $256;
    retain path "&&path&x";
    retain uri "&&uri&x";
    n=1;
    do while (metadata_getnasn(uri,'Members',n,objuri)>0);
      rc=metadata_getattr(objuri,"Name",Name);
      rc=metadata_getattr(objuri,"PublicType",PublicType);
      call symputx(cats('objuri',n),objuri,'l');
      call symputx(cats('objName',n),Name,'l');
      call symputx(cats('objType',n),PublicType,'l');
      output;
      n+1;
    end;
  run;
  proc sql noprint;
  select count(*) into: nobs from &syslast;
  %if &nobs=0 %then %do;
    drop table &syslast;
  %end;
  %else %do objn=1 %to &nobs;
    data _null_;
      infile "C: & cd ""&platform_object_path"" %trim(
        ) & ExportPackage &connx_string %trim(
        )-package ""&root&&path&x\&&objType&objn.._&&objname&objn...spk"" %trim(
        )-objects ""&&path&x/&&objname&objn(&&objType&objn)"" %trim(
        )-log ""&root&&path&x\&&objType&objn.._&&objname&objn...log"" 2>&1" 
        pipe lrecl=1000;
      input; 
      list;
    run;
  %end;
%end;
%mend;

%exporter()

EDIT (April 11th 2020) - I have added two macros to the macrocore library to further enable this:

1. mm_spkexport - will generate the export command

2. mmx_spkexport - will generate the command and execute it (requires XCMD)