fix: mp_loadformat updates by FMTROW

Previously, FMTROW was not being honoured when adding / deleting individual format records.  Updated tests and added additional validations to ensure FMTROW is provided correctly at the input stage.

all.sas

@@ -4189,8 +4189,8 @@ data &cntlout/nonote2err;
   end;
 
   /* create row marker. Data cannot be sorted without it! */
-  if first.fmtname then fmtrow=0;
+  if first.fmtname then fmtrow=1;
-  fmtrow+1;
+  else fmtrow+1;
 
 run;
 proc sort;
@@ -10153,6 +10153,9 @@ select distinct lowcase(memname)
   format, to prevent loss of data - UNLESS the input dataset contains a marker
   column, specifying that a particular row needs to be deleted (`delete_col=`).
 
+  Positions of formats are made using the FMTROW variable - this must be present
+  and unique (on TYPE / FMTNAME / FMTROW).
+
   This macro can also be used to identify which records would be (or were)
   considered new, modified or deleted (`loadtarget=`) by creating the following
   tables:
@@ -10161,7 +10164,7 @@ select distinct lowcase(memname)
   @li work.outds_del
   @li work.outds_mod
 
-  For example usage, see mp_loadformat.test.sas
+  For example usage, see test (under Related Macros)
 
   @param [in] libcat The format catalog to be loaded
   @param [in] libds The staging table to load
@@ -10178,6 +10181,8 @@ select distinct lowcase(memname)
   @param [in] mdebug= (0) Set to 1 to enable DEBUG messages and preserve outputs
 
   <h4> SAS Macros </h4>
+  @li mf_existds.sas
+  @li mf_existvar.sas
   @li mf_getuniquename.sas
   @li mf_nobs.sas
   @li mp_abort.sas
@@ -10228,6 +10233,16 @@ select distinct lowcase(memname)
 %let libcat=%scan(&libcat,1,-);
 
 /* perform input validations */
+%mp_abort(
+  iftrue=(%mf_existds(&libds)=0)
+  ,mac=&sysmacroname
+  ,msg=%str(&libds could not be found)
+)
+%mp_abort(
+  iftrue=(%mf_existvar(&libds,FMTROW)=0)
+  ,mac=&sysmacroname
+  ,msg=%str(FMTROW not found in &libds)
+)
 %let err=0;
 %let msg=0;
 data _null_;
@@ -10248,13 +10263,6 @@ data _null_;
       stop;
     end;
   end;
-  else if name='LIBDS' then do;
-    if exist(value) le 0 then do;
-      call symputx('msg',"Unable to open staging table: "!!value);
-      call symputx('err',1);
-      stop;
-    end;
-  end;
   else if (name=:'OUTDS' or name in ('DELETE_COL','LOCKLIBDS','AUDITLIBDS'))
   and missing(value) then do;
     call symputx('msg',"missing value in var: "!!name);
@@ -10262,6 +10270,14 @@ data _null_;
     stop;
   end;
 run;
+data _null_;
+  set &libds;
+  if missing(fmtrow) then do;
+    call symputx('msg',"missing fmtrow in format: "!!FMTNAME);
+    call symputx('err',1);
+    stop;
+  end;
+run;
 
 %mp_abort(
   iftrue=(&err ne 0)
@@ -10269,6 +10285,15 @@ run;
   ,msg=%str(&msg)
 )
 
+%local cnt;
+proc sql noprint;
+select count(distinct catx('|',type,fmtname,fmtrow)) into: cnt from &libds;
+%mp_abort(
+  iftrue=(&cnt ne %mf_nobs(&libds))
+  ,mac=&sysmacroname
+  ,msg=%str(Non-unique primary key on &libds)
+)
+
 /**
   * First, extract only relevant formats from the catalog
   */
@@ -10322,12 +10347,6 @@ data &inlibds/nonote2err;
     %mp_aligndecimal(end,width=16)
   end;
 
-  /* update row marker - retain new var as fmtrow may already be in libds */
-  if first.fmtname then row=1;
-  else row+1;
-  drop row;
-  fmtrow=row;
-
   fmthash=%mp_md5(cvars=&cvars, nvars=&nvars);
 run;
 

base/mp_cntlout.sas

@@ -79,8 +79,8 @@ data &cntlout/nonote2err;
   end;
 
   /* create row marker. Data cannot be sorted without it! */
-  if first.fmtname then fmtrow=0;
+  if first.fmtname then fmtrow=1;
-  fmtrow+1;
+  else fmtrow+1;
 
 run;
 proc sort;

base/mp_loadformat.sas

@@ -9,6 +9,9 @@
   format, to prevent loss of data - UNLESS the input dataset contains a marker
   column, specifying that a particular row needs to be deleted (`delete_col=`).
 
+  Positions of formats are made using the FMTROW variable - this must be present
+  and unique (on TYPE / FMTNAME / FMTROW).
+
   This macro can also be used to identify which records would be (or were)
   considered new, modified or deleted (`loadtarget=`) by creating the following
   tables:
@@ -17,7 +20,7 @@
   @li work.outds_del
   @li work.outds_mod
 
-  For example usage, see mp_loadformat.test.sas
+  For example usage, see test (under Related Macros)
 
   @param [in] libcat The format catalog to be loaded
   @param [in] libds The staging table to load
@@ -34,6 +37,8 @@
   @param [in] mdebug= (0) Set to 1 to enable DEBUG messages and preserve outputs
 
   <h4> SAS Macros </h4>
+  @li mf_existds.sas
+  @li mf_existvar.sas
   @li mf_getuniquename.sas
   @li mf_nobs.sas
   @li mp_abort.sas
@@ -84,6 +89,16 @@
 %let libcat=%scan(&libcat,1,-);
 
 /* perform input validations */
+%mp_abort(
+  iftrue=(%mf_existds(&libds)=0)
+  ,mac=&sysmacroname
+  ,msg=%str(&libds could not be found)
+)
+%mp_abort(
+  iftrue=(%mf_existvar(&libds,FMTROW)=0)
+  ,mac=&sysmacroname
+  ,msg=%str(FMTROW not found in &libds)
+)
 %let err=0;
 %let msg=0;
 data _null_;
@@ -104,13 +119,6 @@ data _null_;
       stop;
     end;
   end;
-  else if name='LIBDS' then do;
-    if exist(value) le 0 then do;
-      call symputx('msg',"Unable to open staging table: "!!value);
-      call symputx('err',1);
-      stop;
-    end;
-  end;
   else if (name=:'OUTDS' or name in ('DELETE_COL','LOCKLIBDS','AUDITLIBDS'))
   and missing(value) then do;
     call symputx('msg',"missing value in var: "!!name);
@@ -118,6 +126,14 @@ data _null_;
     stop;
   end;
 run;
+data _null_;
+  set &libds;
+  if missing(fmtrow) then do;
+    call symputx('msg',"missing fmtrow in format: "!!FMTNAME);
+    call symputx('err',1);
+    stop;
+  end;
+run;
 
 %mp_abort(
   iftrue=(&err ne 0)
@@ -125,6 +141,15 @@ run;
   ,msg=%str(&msg)
 )
 
+%local cnt;
+proc sql noprint;
+select count(distinct catx('|',type,fmtname,fmtrow)) into: cnt from &libds;
+%mp_abort(
+  iftrue=(&cnt ne %mf_nobs(&libds))
+  ,mac=&sysmacroname
+  ,msg=%str(Non-unique primary key on &libds)
+)
+
 /**
   * First, extract only relevant formats from the catalog
   */
@@ -178,12 +203,6 @@ data &inlibds/nonote2err;
     %mp_aligndecimal(end,width=16)
   end;
 
-  /* update row marker - retain new var as fmtrow may already be in libds */
-  if first.fmtname then row=1;
-  else row+1;
-  drop row;
-  fmtrow=row;
-
   fmthash=%mp_md5(cvars=&cvars, nvars=&nvars);
 run;
 

sasjs/sasjsconfig.json

@@ -67,7 +67,7 @@
     },
     {
       "name": "server",
-      "serverUrl": "https://sas9.4gl.io",
+      "serverUrl": "https://sas.4gl.io",
       "serverType": "SASJS",
       "httpsAgentOptions": {
         "allowInsecureRequests": false

tests/base/mp_loadformat.test.1.sas

@@ -189,8 +189,10 @@ data work.stagedata3;
     if last.fmtname then do;
       output; /* 6 new records */
       x=_n_;
-      x+1;start=cats("mod",x);end=start;label='newlabel1';output;
+      x+1;start=cats("mod",x);end=start;label='newlabel1';fmtrow=fmtrow+1;
-      x+1;start=cats("mod",x);end=start;label='newlabel2';output;
+      output;
+      x+1;start=cats("mod",x);end=start;label='newlabel2';fmtrow=fmtrow+2;
+      output;
     end;
     else if fmtrow le 3 then do; /* 9 more changed values */
       start= cats("mod",_n_);

tests/base/mp_loadformat.test.2.sas

@@ -58,6 +58,9 @@ proc format library=&cat1;
   value agemlb (multilabel)
     19-120='Adults'
     1-18='Children'
+    0-1='Preschool'
+    1-2='Preschool'
+    2-3='Preschool'
     1-4='Preschool';
   value agemlc (multilabel notsorted)
     19-120='Adults'
@@ -67,16 +70,19 @@ run;
 
 %mp_cntlout(libcat=&cat1,cntlout=work.cntlout1)
 %mp_assertdsobs(work.cntlout1,
-  desc=Has 16 records,
+  desc=Has 19 records,
-  test=EQUALS 16
+  test=EQUALS 19
 )
 
 data work.stagedata3;
   set work.cntlout1;
   if fmtname='AGEMLA' and label ne 'Preschool' then deleteme='Yes';
   if fmtname='AGEMLB' and label = 'Preschool' then label='Kids';
-  if fmtname='GENDERML' and label='Farmale' then output;
+  if fmtname='GENDERML' and label='Farmale' then do;
-  output;
+    output;
+    fmtrow=101; output;
+  end;
+  else output;
 run;
 
 
@@ -113,14 +119,17 @@ run;
 
 %let check1=0;
 %let check2=0;
+%let check3=0;
 data test;
   set work.cntlout2;
   where fmtname='GENDERML';
+  putlog fmtrow= label=;
   if _n_=4 and label='Farmale' then call symputx('check1',1);
-  if _n_=5 and label='Farmale' then call symputx('check2',1);
+  if _n_=5 and label ne 'Farmale' then call symputx('check2',1);
+  if _n_=8 and label = 'Farmale' then call symputx('check3',1);
 run;
 %mp_assert(
-  iftrue=(&check1=1 and &check2=1),
+  iftrue=(&check1=1 and &check2=1 and &check3=1),
   desc=Ensuring Farmale values retain their order,
   outds=work.test_results
 )