sasjs/core

Macros for SAS® App Developers

core's Issues

fix licence wording

The following file has an embedded licence: https://github.com/sasjs/core/blob/main/lua/json.lua

When the code is compiled, the licence file appears in the log and it is indistinguishable which part the licence returns to. Some text should be added to make clear that licence applies only to the part that contains the file.

`mp_loadformat()` not appending to audit table

disable dependence on IO (gsub) package

The ml_gsub package does not work in viya 4 (io package disabled)

Need to remove this dependency

mp_searchdata throws warnings when maxobs exceeded

To reproduce:

%mp_searchdata(lib=sashelp, ds=class, string=l,outobs=5)

Log result:

 WARNING: Statement terminated early due to OUTOBS=5 option.
 NOTE: Table MPSEARCH.CLASS created, with 5 rows and 5 columns.
 
 Search query for CLASS took       0.00462007522583 seconds
 WARNING: SQLRC=4 when processing CLASS

Expected : No warnings or errors

create sort-in-place macro

There are certain edge cases when it can be convenient to sort a SAS dataset that has indexes applied, for instance:

• To improve performance in certain cases
• To allow adjacent records to be viewed directly in the dataset
• To reduce dataset size (eg when there are deleted records)

A "sort in place" macro, that would work at both dataset and library level, would be helpful here. This would pick up the existing primary key or unique index and sort on that.

Update `mv_webout()` for Viya 4 WEB approach

When the @sasjs/adapter is used to send requests to SAS Viya using the WEB approach (useComputeApi:undefined|null) then data is condensed across a set of macro variables.

This speeds up the request slightly as it avoids file handling.

In 3.5 we used Lua to convert the macro variables back to CSV files. Unfortunately this fails in Viya 4 due to the lack of a lua IO module:

MPRINT(WEBOUT.MV_WEBOUT):   proc lua;
MPRINT(WEBOUT.MV_WEBOUT):   submit;
MPRINT(WEBOUT.MV_WEBOUT):   run;
<span style="color: blue"></span>
<span style="color: blue">NOTE: Lua initialized.</span>
1                                                          The SAS System                            Tuesday, 29 March 2022 20:15:00

<span style="color: red">ERROR: ...her-2a964713-fda9-4d80-97b4-98a4f5ac5902-n9trj/sasjs.lua:33: attempt to index global &#39;io&#39; (a nil value)</span>
stack traceback:
	...her-2a964713-fda9-4d80-97b4-98a4f5ac5902-n9trj/sasjs.lua:33: in main chunk
	[C]: in function &#39;require&#39;
	SUBMIT block:2: in main chunk
<span style="color: red">ERROR: There was an error submitting the provided code</span>
<span style="color: blue">NOTE: The SAS System stopped processing this step because of errors.</span>
<span style="color: blue">NOTE: PROCEDURE LUA used (Total process time):</span>

To avoid the above error, the mv_webout macro must be converted to data step.

The adapter will also need to reduce the size of the created variables from 65k to 32.7k chars.

create `ddl` folder

More and more macros are depending on data structures, and they cannot keep being added to mp_coretable.sas as this will end up getting very big

Instead we should create a new ddl folder for storing DDL related activity.

We should NOT use this repo for storing data values - it would get extremely big. As and when this becomes necessary, we should create a new repo for that.

make `list` parameter optional in mp_dropmembers.sas

Support STPSRV_HEADER in Base SAS

Provide support for such functions like STPSRV_HEADER .
From log:

ERROR 68-185: The function STPSRV_HEADER is unknown, or cannot be accessed.

mp_storediffs()

A new macro is needed to support a single-source detailed audit history of ALL changes in Data Controller

It should amalgamate the following inputs:

• Deletes
• Appends
• Updates
• Originals

And produce a table in the following format:

create table work.audit_history(
  load_id char(32),
  processed_dttm num format=E8601DT26.6,
  libref char(8),
  dsn char(32),
  key_hash char(32),
  move_type char(1),
  is_pk char(1),
  is_diff char(1),
  tgtvar_type char(1),
  tgtvar_nm char(32),
  oldval_num num,
  newval_num num,
  oldval_char char(32767),
  newval_char char(32767)
);

Enable consul token as param in `mv_registerclient()`

For viya 4, the current mv_registerclient() will fail as the consul token is on a different pod to the compute server.

So in this case we can instead provide the value as a parameter to the macro.

Line breaks not supported in mp_ds2cards()

Currently we are not handling line breaks in the mp_ds2cards.sas macro.

support APPEND operation in mp_binarycopy.sas

This will enable a solution for sasjs/fileuploader#9

mp_copyfolder macro not copying to correct path if source and target root paths are different lengths

The mp_copyfolder macro is currently using the substr function to work out the complete target path for a recursive copy, but it assumes the source and target root paths are the same length - which they might not be, especially if some of the path is parameterised. The result is that sometimes extra text is included in the target path.

Variable __COMMAND is uninitialized

core/base/mp_zip.sas

Lines 52 to 66 in d0a0274

	length __command $4000;
	if file_or_folder='file';
	command=cats('ods package add file="',filepath
	,'" mimetype="application/x-compress";');
	call execute(command);
	run;
	/* tidy up */
	%if &debug=NO %then %do;
	proc sql; drop table &ds;quit;
	%end;
	%end;
	%else %if &type=DATASET %then %do;
	data _null_;
	set ∈
	length __command $4000;

I believe line 54 & 67 should refer to __COMMAND instead COMMAND

mp_getconstraints not returning results

The issue is an upcase() function that appears to have uncovered a bug in SAS: https://communities.sas.com/t5/SAS-Programming/Unexpected-Where-Clause-behaviour-in-dictionary-TABLE/m-p/771554#M244867

Add VARINITCHECK=ERROR to MP_INIT()

As highlighted by SAS legends such as Bruno Müeller and Bill Quinn, the varinitchk option can help avoid data disasters from simple typos.

Adding options varinitchk=ERROR; to the SASjs test suite caused 12/53 tests to fail.

• Add varinitchk=ERROR to mp_init.sas
• Ensure uninitialised variables are fixed in all 12 failing tests

Risky default value for maxtries in %mf_getuniquelibref.sas

base/mf_getuniquelibref.sas has a default value for &maxtries that is too large should it loop that far.

Better still, &maxtries can be dynamic and of no concern to the user:

    %let maxtries=%eval(10**(8-%length(&prefix.))-1);

Additionally, the pre-assignment of the new libname, to point to the Work library location, runs counter to the stated intent of returning an unused libref.

re-apply transactions to a base table

For more info: https://core.sasjs.io/mp__stackdiffs_8sas.html

Macro to COPY an entire directory

We need a macro that will copy an entire directory, including all content and subfolders.

• The directory (recursive) listing should come from mp_dirlist.sas
• The built-in fcopy() function could be helpful, else mp_binarycopy.sas
• There should be a corresponding test

create GET FILE macro for sasjs/server

We need a macro that can fetch a file from SASjs Drive

Macro to lock a table

When performing operations that require multiple passes, it is helpful to mark the corresponding table(s) as locked to prevent data inconsistencies.

The macro should cater for:

• database use
• retry X times with Y gap should the table be locked

support postgres style dates in mp_ds2inserts

These should be added using the E8601DT25.6 format

mf_getapploc fails for testsetup

The logic in mf_getapploc.sas won't work in the sasjs test testsetup (or teardown) - hence, should be adjusted for these specific cases

mp_init autocorrect option

The autocorrect option set in the mp_init macro is set to allow misspellings when the intent is to prevent this correction.

mp_getddl is not working for views

The column detail is extracted, but not the "select from" part

reset syscc in mp_abort for Viya response

When running mp_abort using the compute api approach the response is not being surfaced to the adapter.

It transpires that the reason is the existence of a non-zero SYSCC value results in state of error for the job, as per the extract below.

In order to handle errors gracefully we should reset the value for SYSCC (the final value is displayed anyway in the response json).

{
	"completedTimeStamp": "2021-06-28T20:43:12Z",
	"creationTimeStamp": "2021-06-28T20:43:11Z",
	"id": "EEE43D54-4F36-5243-A103-13FBFDADBD30",
	"jobConditionCode": 1012,
	"links": [{
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30",
		"method": "GET",
		"rel": "self",
		"type": "application\/vnd.sas.compute.job",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/state",
		"method": "GET",
		"rel": "state",
		"type": "text\/plain",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/state"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/state?value=canceled",
		"method": "PUT",
		"rel": "cancel",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/state?value=canceled"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30",
		"method": "DELETE",
		"rel": "delete",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/log",
		"itemType": "application\/vnd.sas.compute.log.line",
		"method": "GET",
		"rel": "log",
		"type": "application\/vnd.sas.collection",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/log"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/listing",
		"itemType": "application\/vnd.sas.compute.log.line",
		"method": "GET",
		"rel": "listing",
		"type": "application\/vnd.sas.collection",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/listing"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/results",
		"itemType": "application\/vnd.sas.compute.result",
		"method": "GET",
		"rel": "results",
		"type": "application\/vnd.sas.collection",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000\/jobs\/EEE43D54-4F36-5243-A103-13FBFDADBD30\/results"
	}, {
		"href": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000",
		"method": "GET",
		"rel": "up",
		"type": "application\/vnd.sas.compute.session",
		"uri": "\/compute\/sessions\/3e53cb30-276c-4b25-9562-ad5409904956-ses0000"
	}],
	"listingStatistics": {
		"lineCount": 0,
		"modifiedTimeStamp": "2021-06-28T20:43:11Z"
	},
	"logStatistics": {
		"lineCount": 1934,
		"modifiedTimeStamp": "2021-06-28T20:43:12Z"
	},
	"sessionId": "3e53cb30-276c-4b25-9562-ad5409904956-ses0000",
	"state": "error",
	"stateElapsedTime": 0,
	"statistics": {
		"systemCpuTime": 0.05,
		"userCpuTime": 0.21
	},
	"version": 1
}

mv_getfoldermembers uses default limit (20)

The limit on mv_getfoldermembers needs to be increased - currently only 20 results are being returned, resulting in this ticket: sasjs/folder-navigator#12

viya 4 macro support

some macro tests are failing in Viya 4

Enable DISPLAY_NAME in sasjs service response

We need a consistent display name in the service response - currently it differs between SAS9 and VIYA.

mp_makedata breaks if there are no character or no numeric variables in the dataset

The mp_makedata macro breaks if there are no character or no numeric variables in the dataset as the argument to the countw function is blank.

fail fast in mv_registerclient

If a user (or backend user) does not have access to the consul token in the Viya Token Generator, no error is presented.

We should fail fast if the operating system account is insufficient when running the mv_registerclient macro.

MF to create a file

Hi Allan, can you create a macro function to create an external file?

mp_jsonout() affected by certain special missings

eg:

To reproduce, export:

proc format library=TEST;
  value days (default=13) ;
  invalue days (default=12) other=42;
run;

`mf_getuser()` fails when the username contains a comma

add stateDetails to mv_jobwaitfor

If a Viya job finishes with a WARNING the state is marked as 'completed' and so an additional response attribute (stateDetails) must be checked to see whether it ran cleanly.

The mv_jobwaitfor.sas macro should be updated to cater for this scenario, changing syscc=4 if the job is in WARNING state, or a higher number for an error.

This line should have the /state endpoint removed:

call symputx(cats('joburi',_n_),substr(uri,1,55)!!'/state','l')

This line:

       headers "Accept"="text/plain"

should change to:

      headers "Accept"="application/json"

The state / state details can then be extracted from the job JSON rather than joburi/state endpoint. proc json could be used, with a temporary libname, eg:

%local libref1; %let libref1=%mf_getuniquelibref();

We can also add the following to prevent further jobs being executed when in an error condition:

 %mp_abort(iftrue=(&syscc > 0)
   ,mac=&sysmacroname
   ,msg=%str(&syscc>0 )
 )

• Job header includes a markdown table describing the outds table
• Outds table includes the statedetails column, whether it is returned or not
• The syscc variable is updated with an appropriate value
• The error / warning message (if any) is printed to the log
• The mf_getuniquelibref macro is in the <h4> SAS Macros </h4> section

More info on stateDetails: https://communities.sas.com/t5/Developers/Extracting-job-state/td-p/700923

The automated release is failing 🚨

🚨 The automated release from the main branch failed. 🚨

I recommend you give this issue a high priority, so other packages depending on you can benefit from your bug fixes and new features again.

You can find below the list of errors reported by semantic-release. Each one of them has to be resolved in order to automatically publish your package. I’m sure you can fix this 💪.

Errors are usually caused by a misconfiguration or an authentication problem. With each error reported below you will find explanation and guidance to help you to resolve it.

Once all the errors are resolved, semantic-release will release your package the next time you push a commit to the main branch. You can also manually restart the failed CI job that runs semantic-release.

If you are not sure how to resolve this, here are some links that can help you:

• Usage documentation
• Frequently Asked Questions
• Support channels

If those don’t help, or if this issue is reporting something you think isn’t right, you can always ask the humans behind semantic-release.

---

Cannot push to the Git repository.

semantic-release cannot push the version tag to the branch main on the remote Git repository with URL https://github.com/sasjs/core.git.

This can be caused by:

• a misconfiguration of the repositoryUrl option
• the repository being unavailable
• or missing push permission for the user configured via the Git credentials on your CI environment

---

Good luck with your project ✨

Your semantic-release bot 📦🚀

Macro to delete an entire directory

We need a macro that will delete an entire directory, including subfolders.

• The directory (recursive) listing should come from mp_dirlist.sas.
• There should be a corresponding test

proc json - some code points did not transcode

ERROR: Some code points did not transcode.
MPRINT(MP_JSONOUT):   run;
ERROR: The JSON writer is in an error state at this point and no more items may be inserted.
ERROR: Due to the previous error, the JSON output file is incomplete and invalid, or in some cases, unable to be created. If created, the JSON output file is retained so that you can review it to help determine the cause of the error.

The above error is typically seen when proc json tries to export a non valid character. We implemented proc json in the sasjs/adapter for speed however this instability is affecting data controller and so we must switch back to the trusty data step.

binary data breaks mp_ds2cards.sas

The current mechanism cannot cope with binary data

To reproduce:

data test;
  format bin $hex500.;
  do x=1 to 250;
   z=byte(x);
   bin=trim(bin)!!z;
  end;
  put bin=;
run;

%mp_ds2cards(base_ds=work.test,showlog=YES
	, cards_file="%sysfunc(pathname(work))/c2.sas"
    , tgt_ds=work.y
    , append=
)

%inc "%sysfunc(pathname(work))/c2.sas"/source2;

proc compare base=test compare=y;
run;

`mp_hashdataset()` fails in specific circumstances

The circumstances are:

1. STRICT mode is enabled (mp_init())
2. The outds= option is used
3. The input data contains missing values

Prep macros for sasjs/server

support special missing values in mp_jsonout.sas

Links to sasjs/adapter#607

Special missing values should be (optionally) converted to strings

support lines over 65k in mv_createfile macro

Macro Scope Test Assertion

When writing macros it's important to ensure that there is no leak in scope.

SAS does not have automatic scope checking, and it is easy to create non-local variables by mistake (leading to hard-to-detect errors).

Therefore, a macro that can snapshot variables before and after a macro execution for running black box SAS tests would be helpful.

`mp_dirlist()` fails un-gracefully when the target directory does not exist

Current log as follows:

 ERROR: Argument 1 to function DOPTNUM(0) at line 74 column 63 is invalid.
 directory=  filepath=  fref=#LN00053 fref2=  file_or_folder=  filename=  ext=  msg=  foption=  level=0 rc=0 did=0 numopts=0 i=. dnum=. midd=. dmsg=  midf=. fmsg=  _ERROR_=1 _N_=1
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.DATA1 may be incomplete.  When this step was stopped there were 0 observations and 7 variables.
 WARNING: Data set WORK.DATA1 was not replaced because this step was stopped.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              715.93k
       OS Memory           26536.00k
       Timestamp           11/03/2022 10:18:18 AM
       Step Count                        27  Switch Count  0
       Page Faults                       2
       Page Reclaims                     171
       Page Swaps                        0
       Voluntary Context Switches        4
       Involuntary Context Switches      0
       Block Input Operations            2344
       Block Output Operations           8
       
 
 ERROR: Variable filepath is not on file WORK.DATA1.

mm_webout failing in WLATIN1 for certain characters

Following the change to use DATASTEP to generate JSON (to support invalid characters) we inadvertently reverted support for certain WLATIN1 characters.

To fix, we can check SYSENCODING - if WLATIN1 then we will use PROCJSON (and drop invalid character support) instead of DATASTEP.

mX_webout() - invalid json caused by missing MEMSIZE

The previous update causes invalid json when MEMSIZE is empty:

    memsize="%sysfunc(INPUTN(%sysfunc(getoption(memsize)), best.),sizekmg.)";
    memsize=""; /* force missing */
    memsize=quote(cats(memsize));
    put ',"MEMSIZE" : ' memsize;

This took a bit longer to debug as the log had no errors - only:

NOTE: Invalid argument to function QUOTE('') at line 23 column 13.

To make this issue easier to discover in future, perhaps the undocumented note2err setting should be applied in mp_init().

mp_searchtool fuzzy match

Hello,

Can the macro find fuzzy matches? . If the variable VSORRES is in multiple datasets but with a different suffix such as VSORRES1, VSORRES_02 etc...how would the macro parameters be filled.

%mp_searchcols(libs=work, cols=vsorres);

Add sysvlong to sasjs response

For debugging client issues it is helpful to immediately understand the version being used. We can add this in the server response.