I initially thought it was easier as well. You can suggest it as a new feature via the SASware ballot. ... View more

The first problem I see is that the data is not in JSON format. Your text file shows lines in the form of: {id=1, kind=de, ser=H2,xid=604,nid=103, ceid=3,nrid=894448f2, redf=1, nans=null,updateddt=2018-11-10}, To be in JSON format, it needs to look like: {"id":1, "kind":"de", "ser":"H2","xid":604, "nid":103, "ceid":3, "nrid":"894448f2", "redf":1, "nans":"null", "updateddt":"2018-11-10"}, Here is your posted data in valid JSON format: [{"id":1, "kind":"de", "ser":"H2","xid":604, "nid":103, "ceid":3,"nrid":"894448f2", "redf":1, "nans":"null","updateddt":"2018-11-10"}, {"id":2, "kind":"de", "ser":"H2","xid":604, "nid":115, "ceid":2,"nrid":"724448f2", "redf":0, "nans":"some data available","updateddt":"2018-11-10"}, {"id":3, "kind":"de", "ser":"H2","xid":604, "nid":125, "ceid":4,"nrid":"854448f2", "redf":1, "nans":"some issues","updateddt":"2018-11-10"}, {"id":4, "kind":"de", "ser":"J2","xid":605, "nid":211, "ceid":3,"nrid":"7881dd3d", "redf":1, "nans":"there is data","updateddt":"2018-11-11"}, {"id":5, "kind":"de", "ser":"J2","xid":605, "nid":217, "ceid":1,"nrid":"ffcb48f2", "redf":1, "nans":"test","updateddt":"2018-11-11"}, {"id":6, "kind":"de", "ser":"J2","xid":605, "nid":217, "ceid":3,"nrid":"810c395d", "redf":0,"nans":"attacked", "updateddt":"2018-11-11"}, {"id":7, "kind":"de", "ser":"J3","xid":605, "nid":187, "ceid":1,"nrid":"540c395d", "redf":0, "nans":"null","updateddt":"2018-11-11"}, {"id":8, "kind":"de", "ser":"J2","xid":605, "nid":193, "ceid":3,"nrid":"48f2310c", "redf":1, "nans":"the bies","updateddt":"2018-11-11"}, {"id":9, "kind":"de", "ser":"J2","xid":605, "nid":199, "ceid":4,"nrid":"5610c395", "redf":1, "nans":"null","updateddt":"2018-11-11"}] As noted previously, you have columns RED, REDF, and REDFL. Since your output example shows only the REDF field, I am going to assume that all three of these column names are actually the same column. If you are able to get the data into valid JSON form, I found that the JSON libname engine worked fine: libname x json './sasuser/sasComm700502.json'; proc print data=x.root(drop=ordinal_root); run; Here are the results: Obs id kind ser xid nid ceid nrid redf nans updateddt 1 1 de H2 604 103 3 894448f2 1 null 2018-11-10 2 2 de H2 604 115 2 724448f2 0 some data available 2018-11-10 3 3 de H2 604 125 4 854448f2 1 some issues 2018-11-10 4 4 de J2 605 211 3 7881dd3d 1 there is data 2018-11-11 5 5 de J2 605 217 1 ffcb48f2 1 test 2018-11-11 6 6 de J2 605 217 3 810c395d 0 attacked 2018-11-11 7 7 de J3 605 187 1 540c395d 0 null 2018-11-11 8 8 de J2 605 193 3 48f2310c 1 the bies 2018-11-11 9 9 de J2 605 199 4 5610c395 1 null 2018-11-11 ... View more

This looks like JSONL. You should probably read this blog by @ChrisHemedinger. ... View more

Another possibility that should easily resolve your macro variables and leave out the worry of JSON syntax issues is to use the JSON procedure (available in SAS 9.4 maintenance 4 or later). Here is code that will produce the same form of JSON output. I had to guess on the values you were inserting, but resolving the macros should be easy with PROC JSON.   %let name=myName; %let dataTableUri=https://www.google.com; %let target=myTarget; %let pipelineHref=myHref; %let pipelineUri=https://pipe.line.uri; proc json out='./sasuser/jsonTest.json' nosastags pretty; write open object; /* object 1 */ write values name "&name"; write values dataTableUri "&dataTableUri"; write values type "predictive"; write values pipelineBuildMethod "template"; write values analyticsProjectAttributes; write open object; /* object 2 */ write values targetVariable "&target"; write values partitionEnabled True; write values targetEventLevel "1"; write close; /* object 2 */ write values settings; write open object; /* object 3 */ write values applyGlobalMetadata "false"; write close; /* object 3 */ write values links; write open array; /* array 1 */ write open object; /* object 4 */ write values method "GET"; write values rel "initialPipelineTemplate"; write values href "&pipelineHref"; write values uri "&pipelineUri"; write values type "application/vnd.sas.analytics.pipeline.template"; write close; /* object 4 */ write close; /* array 1 */ write close; /* object 1 */ run; OUTPUT FILE: { "name": "myName", "dataTableUri": "https://www.google.com", "type": "predictive", "pipelineBuildMethod": "template", "analyticsProjectAttributes": { "targetVariable": "myTarget", "partitionEnabled": true, "targetEventLevel": "1" }, "settings": { "applyGlobalMetadata": "false" }, "links": [ { "method": "GET", "rel": "initialPipelineTemplate", "href": "myHref", "uri": "https://pipe.line.uri", "type": "application/vnd.sas.analytics.pipeline.template" } ] } ... View more

@ChrisHemedinger is correct about the usage of IF-THEN logic. If you examine the syntax for PROC JSON you will not see support for the IF statement. As Chris notes, the data needs to be prepared the way you want it prior to using PROC JSON. A program I wrote showing how to produce hierarchical JSON output in PROC JSON demonstrates preparing of the data. This maybe the article you already found. If not, here is the link: Creating a hierarchical JSON file  ... View more

This solution produces valid JSON but not exactly in the format you specified. I am not sure how strict your requirements are for the output. The advantage is that the code is simpler since once the data is in a SAS data set, PROC JSON produces the valid JSON output. I borrowed and slightly modified the DATA step that @Ksharp wrote to put the data into a SAS data set.   data have; sample ="The following are the graphical (non-control) characters defined by ISO 8859-1 (1987). DESCRIPTION : in words aren't all that helpful, but they're the best we can do in text. A graphics file illustrating the character set should be available from the same archive as this file.RESULT :success INTERPRETATION : ISO 8859-1 (1987).CREATED_BY:Questy.CREATED_ON:29/07/1963"; run; data work.test(drop=sample a1-a5); set have; a1=find(sample,'DESCRIPTION'); a2=find(sample,'RESULT'); a3=find(sample,'INTERPRETATION'); a4=find(sample,'CREATED_BY'); a5=find(sample,'CREATED_ON'); DESCRIPTION=substr(sample,a1+14,a2-a1-14); RESULT=substr(sample,a2+8,a3-a2-8); INTERPRETATION=substr(sample,a3+16,a4-a3-16); CREATED_BY=substr(sample,a4+11,a5-a4-11); CREATED_ON=substr(sample,a5+11); run; proc json out='c:\temp\x.json' nosastags pretty; export work.test; run; This is the resultant JSON file: [   {     "DESCRIPTION": " in words aren't all that helpful,but they're the best we can do in text. A graphics file illustratingthe character set should be available from the same archive as thisfile.",     "RESULT": "success",     "INTERPRETATION": " ISO 8859-1 (1987).",     "CREATED_BY": "Questy.",     "CREATED_ON": "29/07/1963"   } ] ... View more

@DavePrinsloo, as a member of the FCMP development team, we would like to clear up something mentioned in your post. Once the DATA step encounters an FCMP function or subroutine, the FCMP code is located in the specified FCMP function library (OPTION CMPLIB), the needed FCMP code is loaded into memory, and compiled. The DATA step then calls the FCMP code as it would with any other SAS function. The compiled FCMP function code remains in memory until the end of the DATA step. There are no new SAS sub-sessions created when using FCMP. ... View more

Using your supplied description of the data, I created a DATA step to randomly create test data. I then used the code in your FCMP subroutine to also produce a DATA step that will run the same code outside of FCMP. I then ran the FCMP subroutine and the DATA step against various sizes of the data. The timing numbers are from SAS log of only the DATA steps actually checking the codes. It was run at SAS 9.4 maintenance 6 and showed the following: Observations       FCMP Soubroutine                   DATA step   count        Real time      CPU Time        Real Time      CPU Time     1,000      0.11 seconds   0.09 seconds    0.05 seconds   0.03 seconds     10,000     0.14 seconds   0.07 seconds    0.07 seconds   0.06 seconds    100,000     0.55 seconds   0.48 seconds    0.35 seconds   0.32 seconds  1,000,000     4.70 seconds   4.67 seconds    3.15 seconds   3.15 seconds 10,000,000    44.27 seconds  44.25 seconds   31.06 seconds  31.04 seconds While the FCMP subroutine implementation did run slightly slower than the DATA step, I'm not seeing the vast time discrepancies that you are seeing. Does my test faithfully recreate what you are doing? Note that since I use a seed value in the STREAMINIT call, you should be able to run the same code to produce the same data and compare your timings to what I'm seeing.   data RandomData; length code $5; call streaminit(12345); do i = 1 to 1000; codeType = rand("Integer", 1, 3); /* requires SAS 9.4M5 or later */ if (codeType EQ 1) /* up to 3 digit number */ then code = rand("Integer", 0, 999); if (codeType EQ 2) /* up to 3 digit number followed by a letter */ then do; number = rand("Integer", 0, 999); letter = byte(int(rand("Integer", 65, 90))); code = CATS(number,letter); end; if (codeType EQ 3) /* 3 digit number, a "-", and a single digit number */ then do; number3digits = rand("Integer", 0, 999); number1digit = rand("Integer", 0, 9); code = CATS(number3digits, '-', number1digit); end; output; end; run; LIBNAME myfuncz 'C:\temp'; proc fcmp outlib=myfuncz.turnout43ge.pdprx; subroutine pdprx(ps_code$, pd_num, pd_num_sfx$, split_sfx$); outargs pd_num, pd_num_sfx, split_sfx; re = prxparse("/^(\d+)$|^(\d+-\d)$|^(\d+[A-Z]+)$/"); re2 = prxparse("/^(\d+)-(\d)$/"); re3 = prxparse("/^(\d+)([A-Z]+)$/"); pd_num=.; pd_num_sfx=""; split_sfx=""; if prxmatch(re, ps_code) then do; wc = prxparen(re); /* put ps_code wc; */ select (wc); when (1) pd_num=input(ps_code, best32.); when (2) do; dummy = prxmatch(re2, ps_code); call prxposn(re2, 1, pos, len); pd_num=input(substr(ps_code, pos, len), best32.); call prxposn(re2, 2, pos, len); pd_num_sfx=substr(ps_code, pos, len); end; when (3) do; dummy = prxmatch(re3, trim(ps_code)); call prxposn(re3, 1, pos, len); pd_num=input(substr(ps_code, pos, len), best32.); call prxposn(re3, 2, pos, len); split_sfx=substr(ps_code, pos, len); end; otherwise; end; end; endsub; quit; OPTION CMPLIB=myfuncz.turnout43ge; data codesOutFcmp(keep=code pd_num pd_num_sfx split_sfx); length pd_num_sfx $1. split_sfx $1.; set RandomData; call pdprx(compress(code), pd_num, pd_num_sfx, split_sfx); output; run; data codesOutDS(keep=code pd_num pd_num_sfx split_sfx); length pd_num_sfx $1. split_sfx $1.; set RandomData; /* Same code as in FCMP function */ re = prxparse("/^(\d+)$|^(\d+-\d)$|^(\d+[A-Z]+)$/"); re2 = prxparse("/^(\d+)-(\d)$/"); re3 = prxparse("/^(\d+)([A-Z]+)$/"); pd_num=.; pd_num_sfx=""; split_sfx=""; if prxmatch(re, code) then do; wc = prxparen(re); /* put ps_code wc; */ select (wc); when (1) pd_num=input(code, best32.); when (2) do; dummy = prxmatch(re2, code); call prxposn(re2, 1, pos, len); pd_num=input(substr(code, pos, len), best32.); call prxposn(re2, 2, pos, len); pd_num_sfx=substr(code, pos, len); end; when (3) do; dummy = prxmatch(re3, trim(code)); call prxposn(re3, 1, pos, len); pd_num=input(substr(code, pos, len), best32.); call prxposn(re3, 2, pos, len); split_sfx=substr(code, pos, len); end; otherwise; end; end; output; run; ... View more