topic Re: Need to use dataset to generate JSON file in SAS Programming

Need to use dataset to generate JSON file

niejung — Wed, 22 Jul 2020 02:36:34 GMT

I saw some posts to generate JSON file using Proc JSON write object write array . I am able to do so. When I want to replace array container with dataset value, then I got stuck.

I want to replace

write values "FruitName" "Orange";

write values "FruitName" %fruit_name;

I tried this and did not work

if first.fruit then

do;

write open object;
write values "FruitName" trim(%fruit_name);
write close;

end;

How can I fix it?

proc import datafile="&path/mapping_fruit.csv" 
                 out=fruit replace
                 dbms=csv replace;
run;

proc sort data=fruit;
     by id_fruit;
run;

proc print data=fruit;
run;
proc json out="fruit.json" pretty nokeys nosastags;

write open object; 
	write values "FruitMetaData" ;
	write open object;
	        write values "RequestTime" "06/18/2020 09:10:10";
	write close;
	write values "Fruitload" ;
	write open object;
	        write values "FruitItems" ;
		        write open array; 
					write open object;                              
			        write values "FruitName" "Apple"; 
					write close;
					write open object;   
					write values "FruitName" "Orange";
					write close;
					write open object; 
					write values "FruitName" "Banana";    
					write close;
					write open object; 
					write values "FruitName" "Lemon";                
			        	write close;                    
		        write close;
	write close; 
write close;
run;

Re: Need to use dataset to generate JSON file

andreas_lds — Wed, 22 Jul 2020 04:57:17 GMT

It "did not work" can mean anything, are there errors, warnings or unexpected notes in the log?

Re: Need to use dataset to generate JSON file

niejung — Wed, 22 Jul 2020 11:13:29 GMT

It doesn't like it when I changed to use loop through dataset.

proc import datafile="/fmac/dev/cor/cwa/cfg/mapping_fruit.csv"
                 out=fruit replace
                 dbms=csv replace;
run;

proc sort data=fruit;
     by id_fruit;
run;

proc print data=fruit;
run;
proc json out="fruit.json" pretty nokeys nosastags;

write open object;
        write values "FruitMetaData" ;
        write open object;
                write values "RequestTime" "06/18/2020 09:10:10";
        write close;
        write values "Fruitload" ;
        write open object;
                write values "FruitItems" ;
                        write open array;
                                if first.fruit then;
                                        do ;
                                        write open object;
                                        write values "FruitName" &Fruit_Name;
                                        write close;
                                end;
                        write close;
        write close;
write close;
run;

Error:

43 write open array;
44 if first.fruit then;
__
180
NOTE: The previous statement has been deleted.
45 do ;
__
180
NOTE: The previous statement has been deleted.
ERROR 180-322: Statement is not valid or it is used out of proper order.
46 write open object;
NOTE: The previous statement has been deleted.
47 write values "FruitName" &Fruit_Name;
_
22
200
WARNING: Apparent symbolic reference FRUIT_NAME not resolved.
NOTE: The previous statement has been deleted.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, ;, /, F, FALSE, N, NULL, T, TRUE.
ERROR 200-322: The symbol is not recognized and will be ignored.
48 write close;
NOTE: The previous statement has been deleted.
49 end;
___
180

Re: Need to use dataset to generate JSON file

ChrisHemedinger — Wed, 22 Jul 2020 11:53:22 GMT

I don't think PROC JSON supports that DATA step style of if-then logic. You would need to capture this in a DATA step to prepare the data first, then use PROC JSON to write out the values from the records.

Or you might be able to write the data in pieces by subsetting in the EXPORT statement, as in this example.

Re: Need to use dataset to generate JSON file

niejung — Wed, 22 Jul 2020 14:47:51 GMT

😭 I did find a awesome article that demonstrated to use data step and create PROC JSON command into a file. I will try that. Luckily I am only need to deal with one level hierarchy. I will do a quick rewrite and test it.

(I will have to find that link again and share to here late)

Re: Need to use dataset to generate JSON file

BillM_SAS — Wed, 22 Jul 2020 15:44:12 GMT

@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

Re: Need to use dataset to generate JSON file

niejung — Wed, 22 Jul 2020 18:47:28 GMT

Yes. That was the link I looked yesterday. It is well written. Thank you so much for sharing it again.

Re: Need to use dataset to generate JSON file

niejung — Thu, 23 Jul 2020 01:37:40 GMT

Thank you so much for the help! I got it to work. The code is messy but I will do some clean up later. @BillM_SAS @ChrisHemedinger

/** Create mapping dataset **/
%let path=/cfg;
options validvarname=v7;

proc import datafile="&path/mapping_fruit.csv"
			out=fruit
			dbms=csv replace;
run;


proc print data=fruit;
run;

data shoes;
	set fruit;
run;

proc print data=shoes;
run;

/*****************************

* Modifiable macro variables

******************************/
%let jsonProcCodeSpec=jsonProcCode.sas;
%let jsonOutputSpec=jsonOutput_all.txt;
%let sourceDataSet=Shoes;

/*****************************

* Constant macro variables

******************************/
%let stmtEnd=%STR(;);

data wastedGuids;
  guid = uuidgen();
run;

proc print data=wastedGuids;
run;


%macro fileHeader(filePath);
	put "proc json pretty out=""" &filePath """ nosastags &stmtEnd";
	put "write open object &stmtEnd ";
        guid = uuidgen();
	put "write values ""FruitMetaData"" &stmtEnd";
	put "write open object &stmtEnd";
	valueStmt=CAT('write values "CorrelationIdentifier" "', TRIM(guid),'";' );
	put valueStmt;
	put "write values ""RequestTime"" ""06/18/2020 09:10:10""  &stmtEnd";
	put "write values ""FruitRequestId"" ""10000"" &stmtEnd";
	put "write close  &stmtEnd";
	put "write values ""FruitRequestPayload"" &stmtEnd";
	put "write open object  &stmtEnd";
	put "write open array &stmtEnd"; 
                    

%mend fileHeader;


%macro fileFooter();
	put "write close &stmtEnd ";
	put "write close &stmtEnd ";
	put "write close &stmtEnd ";
	put "run &stmtEnd";
%mend fileFooter;

data _null_;
	set shoes end=lastOne;

	/* specifies the output file for PUT statements */
	FILE "&jsonProcCodeSpec" DISK;



	/* Only on the first observation in the data set, write the required
	initial statements to the JSON procedure code file. */
	if _N_ eq 1 then
		do;
			%fileHeader("&jsonOutputSpec");
		end;

	put "write open object  &stmtEnd";
	valueStmt=CAT('write values "Fruit Name" "', TRIM(fruit_name),'";' );
	put valueStmt;
	put "write close  &stmtEnd"; 


	if lastOne then
		do;
			%fileFooter();
		end;
run;

%include "&jsonProcCodeSpec";