proc JSON export

[grimol]

Hello,

I try to output a JSON-File from three different tables.

What I want is this:

{
"ID":"1",
"Status":"2",
"Rechtsform_ID":"3",
  "Mitarbeiter": [
    {
      "WERT": "0",
      "QS": "X.99.9"
    },
    {
      "WERT": "1",
      "QS": "X.99.9"
    }
  ],
  "EFE": [
    {
      "FUNKTIONSART": "2",
      "PROXY": "0",
      "PERSKEYS": "AAA1BB2CCC",
      "GEBDAT": "1970-08-12",
      "EINTRITT": "2011-08-12"
    },
    {
      "FUNKTIONSART": "2",
      "PROXY": "0",
      "PERSKEYS": "xxxxxx",
      "GEBDAT": "1970-08-12",
      "EINTRITT": "2011-08-12"
    }
  ]
}

But I have a problem with the first part (table sub_main). How can I export this, without an  array (just key-value)?

This is the program I have:

data sub_main;
ID="1";Status="2";Rechtsform_ID="3";output;
run;

data sub_ma;
WERT="0"; QS="X.99.9"; output;
WERT="11"; QS="X.99.9"; output;
run;

data sub_efe;
      FUNKTIONSART="2";
      PROXY="0";
      PERSKEYS="AAA1BB2CCC";
      GEBDAT="1970-08-12";
      EINTRITT="2011-08-12";
	  output;
	  FUNKTIONSART="2";
      PROXY="0";
      PERSKEYS="xxxxxx";
      GEBDAT="1970-08-12";
      EINTRITT="2011-08-12";
	  output;
run;
proc json out="D:\Output_HAVE.json" pretty  nosastags;
   write open object;
	/*   	write vaules "ID" "1";*/ /*<<<< works, bust its "hard coded*/

   /*		export sub_main*/ /*<<<<< dont work ;(*/

	   write values "Mitarbeiter";
	   	write open array;
	   	export sub_ma;
	   write close;
	 
	   write values "EFE";
	   	write open array;
	   	export sub_efe;
	   write close; 
   write close; 

run;

And thats the result:

{
  "Mitarbeiter": [
    {
      "WERT": "0",
      "QS": "X.99.9"
    },
    {
      "WERT": "1",
      "QS": "X.99.9"
    }
  ],
  "EFE": [
    {
      "FUNKTIONSART": "2",
      "PROXY": "0",
      "PERSKEYS": "AAA1BB2CCC",
      "GEBDAT": "1970-08-12",
      "EINTRITT": "2011-08-12"
    },
    {
      "FUNKTIONSART": "2",
      "PROXY": "0",
      "PERSKEYS": "xxxxxx",
      "GEBDAT": "1970-08-12",
      "EINTRITT": "2011-08-12"
    }
  ]
}

[BillM_SAS]

You will need to do a little extra processing to get the JSON output you desire. Instead of hardcoding the names and values from the 'sub_main' dataset, I used the SQL Dictionary tables to dynamically obtain the needed data. I then stored the data in macro variables and used those in the JSON procedure. This should give you an idea of a way to solve your problem.

proc sql;
select name into :colName1 - :colName3 
from dictionary.columns
where libname = 'WORK' and memname = 'SUB_MAIN'
;
quit;
 %put &colName1, &colName2, &colName3;

proc sql;
select id, status, Rechtsform_ID into  :colValue1, :colValue2, :colValue3
from WORK.SUB_MAIN
;
quit;
%put &colValue1, &colValue2, &colValue3;

proc json out="output.json" pretty  nosastags;
   write open object;
     write values &colName1 &colValue1;
     write values &colName2 &colValue2;
     write values &colName3 &colValue3;

	   write values "Mitarbeiter";
	   	write open array;
	   	export sub_ma;
	   write close;
	 
	   write values "EFE";
	   	write open array;
	   	export sub_efe;
	   write close; 
   write close; 

run;

[grimol]

OK, I thought this comming. But WHY? SAS has a export-function in JSON, so WHY do I need to put values in macrovariables.... Thumbs down for SAS ;(

[BillM_SAS]

All software has limitations for various reasons. If you are unhappy with this limitation, you could submit your idea as a SASware Ballot entry.