DATA Step, Macro, Functions and more

JSON library handling complex system dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

JSON library handling complex system dates

This post is to discuss how to deal with date value informats on using the SAS JSON library map. The example date we are referring to is \/Date(1510426107000)\/. See details below.

We just installed 9.4 on our desktop and we are utilizing the new proc http to download sharepoint data. This required assistance from our sas rep (very thankful for the guidance) to run sas as a Unicode server. Without that the JSON library was failing on escaped unicode values.

Our current issue is dealing with the automap and map of the JSON data and complex dates. With all things SAS, you can do thing multiple ways. I’m finding that the dates that we need to convert may need to be importing from the JSON data and then converted in a sas datastep to the correct sas date time variable. I had planned on creating a macro to take in a string of variables, convert the data, drop the original variables, then rename the variables to the original name (e.g., with proc datasets). While this is very valid, it seems like another strait forward approach could be used.

I’m unable to find the documentation of the informats used for the JSON library map setup. What would be ideal is to be able to modify the map used (which can be done) to tell SAS the correct format to read and convert to a sas date. This could be present now or maybe a future release.

While googling for an answer, here is what I found to match the format of the date that comes from sharepoint "\"\\/Date(1335205592410)\\/\"" .NET JavaScriptSerializer (https://stackoverflow.com/questions/10286204/the-right-json-date-format).

Here is the date that come frim sharepoint using the modifieddate
So this date in JSON: "Modified": "\/Date(1510426107000)\/"

Is automapped to character by default.

In the map file, I changed the mapping to reflect the incorrect iso format date:

{
"NAME": "Modified",
/*"TYPE": "CHARACTER",*/
"TYPE": "NUMERIC",
"INFORMAT": [ "IS8601DT", 19, 0 ],
"FORMAT": ["DATETIME", 20],
"PATH": "/root/d/results/Modified",
"CURRENT_LENGTH": 21
},

Accepted Solutions
Solution
‎01-30-2018 01:13 PM
Occasional Contributor
Posts: 9

Re: JSON library handling complex system dates

Posted in reply to dwaynejarman

Here is a macro I developed to deal with the SharePoint 2010 data download.  It took me some time to test as this appears to be working quite nicely.  Feel free to use this as is with no warranty implied.

 

/**************************************************************************************************************************
Read me:

SharePoint has a 1,000 record limit when serving data via rest in SharePoint 2010.  This limitation may be removed in future
situations, but this code is created so the it pulls the first 1,000 records then continues to pull the records matching the 
filter criteria until all the records matching the criteria are recieved.  

1. This macro uses the C:\temp folder so, if it is not in place it will be created.
2. When you run this macro on a list for the first time, you will need to look for the map file (in C:\temp) and modify it if 
   the table does not load all the data in the correct format that you are expecting, you can make adjustments.  Note: dates 
   will start and end with /Date( and )/ which getsstripped by the macro and converted to a SAS date.  Here is how a formatted date field should look like in the map file:

        {
          "NAME": "FieldNameInSharePoint",
          "TYPE": "CHARACTER",
          "PATH": "/root/d/results/FieldNameInSharePoint",  
          "CURRENT_LENGTH": 21
        },

	Note: you may have to go through a larger request then acutally needed to get the correct formats and/or
    manually put the type and current length in for fields that are KNOWN to be dates.  The TYPE AND CURRENT_LENGHT are
	typically the fields to check and adjust.

3. File created using SAS v9 Maintenance package 4.3.
**************************************************************************************************************************/

*Details of this macro are covered in the Download SharePoint 2010 data.sas file;
%macro createData(u=,list=,append=,convertdates=,usemymap=);
	%let dir = "c:\temp";
	*If the temp folder exists in C, use it, otherwise create it;
	%if %sysfunc(fileexist(&dir)) %then %do;       
	%end;                                          
	%else %do;
		dcreate("temp",'c:\');
   	%end;
	%let lname = &list;
	%if &append = No %then %do;
	   	%if %sysfunc(exist(&list)) %then %do;
			proc datasets lib=work nolist; delete &list; quit; run;
		  	%put NOTE: Deleted &list from WORK;
	   	%end;
	%end;
	*If the next variable is found below, come back up to process more records;
	*Jumping to this section allows you to avoid the table deletion call;
	%getmorerecords:

	*reset rowCount;
	%let rowCount = 0;
	%if &usemymap ne '' %then %do;
		%let map = "c:\temp\&usemymap._json.map";
	%let map = "c:\temp\&list._json.map";
	%end;                                 
	%else %do;
		%let map = "c:\temp\&list._json.map"; 
   	%end;

	filename minmap ↦
	*filename minmap "c:\temp\&list._json.map";
	*filename topics temp;

	filename topics "C:\temp\&list._json.txt" encoding="UTF-8";

	%put NOTE: calling data from &=u;
	*call the data from sharepoint; 
	proc http url=&u method="GET" out=topics AUTH_NEGOTIATE;
	headers
		"Accept"="application/json;odata=verbose" 
		"ContentType "="application/json;odata=verbose;charset=utf-8";
	run;

	%put NOTE: Assigning JSON and posts libref;
	/* Let the JSON engine do its thing */
	libname posts JSON fileref=topics map=minmap;* automap=replace;

	*If the map exists, use it, otherwise autocreate it;
	%if %sysfunc(fileexist(&map)) %then %do;       
		libname posts JSON fileref=topics map=minmap;* automap=replace;  
	%end;                                          
	%else %do;
		%put NOTE: Placing the map of loaded JSON formats in ↦
		libname posts JSON fileref=topics map=minmap automap=replace;  
   	%end;
	%if %sysfunc(libref(posts)) ne 0 %then %do;
		 %put ERROR: The SAS Data Library could;
		 %put ERROR- not be established;
		 %goto exit;
	%end;  

	%put NOTE: Checking for the __next link and creating the Next table in WORK;
	PROC SQL;
		CREATE TABLE NEXT AS SELECT __next as URL, "&list" || substr(__next,find(__next,'=',-length(__next))+1) as start from posts.D; 
	Quit;

	*If the table exists, then merge the results into the table, othewise create it.;
   	%if %sysfunc(exist(&list)) %then %do;
		%put NOTE: Merging new results with previous results;
		PROC SQL;
			create table list2 as select * from &list union select * from posts.D_results;
			create table &list as select * from list2;
		QUIT;
		run;
	%end;
	%else %do;
		%put NOTE: Creating &=list from posts.D_results;
		data &list; set posts.D_results; run;
   	%end;
	/*delete list2 if it exist, which it should in this case;*/
	%if %sysfunc(exist(list2)) %then %do;
		%put NOTE: Deleting list2 from WORK;
		proc datasets lib=work nolist; delete list2; quit;
	%end;
	/**Create a macro variable to hold the row count;*/
  	%local rowCount;
	%if %sysfunc(exist(Next)) %then %do;
	  	/*Query the record count from posts.D, then there are more records; */
		%put NOTE: Counting records in Next Table in WORK;
	  	proc sql noprint;
			select count(*) into :rowCount trimmed from Next; *&dsn;
			select URL into :preu trimmed from Next; *&dsn;
	 	quit;
		%if &rowCount eq 0 %then %do;
			%let u = ;
		%end;
		%else %do;
			%let u = "&preu";
		%end;

	%end;
	/*%else;
		%goto exit;
	%end;*/

  	%put NOTE: &=rowCount, Next link &=u;
	%put NOTE: Clearing libname references posts and JSON;
	libname posts clear; 
	/*delete list2 if it exist, which it should in this case;*/
	%put Releasing the references to minmap, topics, and posts so they can be reassigned on the next loop
	filename minmap clear;
	filename topics clear;
	libname posts clear;

	*If there is a row in posts.D then, set the url macro variable to the next set an the next table name as the skip token; 
	/*Start back up at the top to process another set of records;*/  	
	/*Replace the variables that are used to get and process data based on the next parameter;*/
	%if &rowCount > 0 %then %do;
		%if %sysfunc(exist(Next)) %then %do;
			%put NOTE: Deleting Next from WORK;
			proc datasets lib=work nolist; delete Next; quit;
		%end;
		%goto getmorerecords;
	%end;

	/**************************************************************************************************************************
	Look for all columns that are character 21 characters in lenght to see which columns need to be changed into a date format;
	**************************************************************************************************************************/

	%if &convertdates = Yes %then %do;
		%let ulist = %UPCASE(&list);
		proc sql noprint;
			create table tablesettings as select * from dictionary.columns where upcase(libname) = "WORK" and upcase(memname)="&ulist";
			create table nondates as select name,varnum,'nondate' as type from tablesettings where name not in ("ordinal_d","ordinal_results");
			create table dates as select name,varnum,'date' as type from tablesettings where type="char" and length=21;
			select count(*) into :datecount from dates;
		quit;
		%if  &datecount > 0 %then %do;
		  	proc sql noprint;
			  select distinct name into : name_list separated by ' ' from dates;
			quit;
		  	%local i next_name;
			%do i=1 %to %sysfunc(countw(&name_list));
			   %let next_name = %scan(&name_list, &i);
			   proc sql noprint;
			      select count(*) as count into :hasdates from &list where  &NEXT_NAME LIKE "/Date(%";/*"%Date(%";*/
			   quit;
				%if  &hasdates > 0 %then %do;
					*Delete the date field from the dates table;
					data nondates; set nondates; if name = "&NEXT_NAME" then delete; run; 
				%end;
	         	%else %do;
					/*%put &NEXT_NAME is NOT a date field;*/
					data dates; set dates; if name = "&NEXT_NAME" then delete; run; 
	            %end;
			%end;
			proc sql; create table columns as select * from nondates outer union corr select * from dates order by varnum; quit;
		%end;
	    %else %do;
			proc sql; create table columns as select * from nondates order by varnum; quit;
	    %end;
		proc sql noprint;
			select case
				when type = 'date' then trim("dhms('01jan1970'd,0,0,(input(tranwrd(tranwrd(" || trim(name) || ", '/Date(', ''),')/',''),14.))/1000 + TZONEOFF()) AS " || trim(name) || " format=datetime.")
		        else name
		        end as col into :col_list  separated by ", "
			from columns;
			create table &list.2 as
				select &col_list from &list;
			quit;
		quit;
		proc datasets lib=work nolist; delete Columns Dates Nondates Tablesettings; quit; run;
		proc datasets lib=work nolist; delete &list; quit; run;
		proc datasets lib=work nolist nowarn; change &list.2=&list; run; quit;	
	%end;


%exit:
%mend;


/**************************************************************************************************************************
How to use this macro:
%let url = your url to your list of interest;
Example: %createData(u=&url,list=rficfirm,append=Yes,usemymap=firm);
1. %createData() 
   This is now to call the macro (in general).  Follow instructions below to specify the settings.
2. u=&url 
   use the %let url = to reference the URL to download the sharepoint data (required)
3. list= 
   This is really a reference to the sharepoint list (required).  While it may
   be helpful to have the same name as the list in SharePoint, you can give it any name you like.
   Note: all references loaded in C:\temp will start with the value you use for list=
4. append=
   So, if you were to merge multiple queries, you may want to continue loading the data to the 
   same table.  However, if you want to continuously replace the table everytime you run the macro
   then you say append=No.  append=No will delete the table at the start of the macro so that
   if you were testing and you keep modifying your url, you are not constantly loading the data
   to the table causing duplicate records.
5. usemymap=
   If you are trying to download multiple different files of the same dataset, for example in one dataset there are 
   nearly 10,000 records that I need, but I want to keep them separate.  The have the same map, but I want the records 
   in different tables.  This allows you to give a different table name in 3 above but use the same map as 3 above. 
5. Note: SAS has been programmed to check all Characters fields lenght $21. to see if they contain /Date(
   If they do, then /Date( and )/ are removed and the fields are converted to SAS date format.  The 
   tabled referenced in 3. above is then reformatted in the original order that was downloaded so that 
   the user is able to get the table with the correct format irregardless of the UNIX date stamp provided
   by SharePoint. 

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

View solution in original post


All Replies
Community Manager
Posts: 3,349

Re: JSON library handling complex system dates

Posted in reply to dwaynejarman

Sounds like you've done a lot of research on this.  

 

The date format that you found SharePoint uses looks like "number of milliseconds since Jan 1, 1970".   A SAS datetime is the number of seconds since Jan 1, 1960

 

If that's the case, then you should be able to a custom informat to read it (necessary, I think, because of the VDate / V delimiting wrapper on the value in the JSON).  A custom informat can skip over that bit, bring in the value as a number and divide by 1000, subtract 10 years worth of seconds, resulting in a valid SAS datetime.  (I covered a similar topic here about converting Microsoft datetime values to SAS.)

 

I haven't tested the JSON libname with user defined informats -- I'm hoping that it works just as you'd expect.  Make sure the OPTIONS FMTSEARCH has the informat location in the path, and then specify that informat in the JSON map you create.

 

 

 

 

Occasional Contributor
Posts: 9

Re: JSON library handling complex system dates

Posted in reply to dwaynejarman
I believe that is what I’m looking for. Our SAS rep used the Unix conversion that is in another paper to calculate the correct date/time. I’m not quite sure how to write a custom informat for the map to be parsed in the JSON library. If there is an example available, I would be happy to do some testing. If one exists, please share. Thanks for the reply.
Occasional Contributor
Posts: 9

Re: JSON library handling complex system dates

Posted in reply to dwaynejarman
This is the Unix date example I mentioned that could be used as a base for the custom format. https://blogs.sas.com/content/sasdummy/2015/04/16/how-to-convert-a-unix-datetime-to-a-sas-datetime/

I think what you were saying was to create a custom format for sas to use to parse the date in JSON. I’m not quite sure how to create the custom format for the date but the link above I think gives a formula to make one.
Solution
‎01-30-2018 01:13 PM
Occasional Contributor
Posts: 9

Re: JSON library handling complex system dates

Posted in reply to dwaynejarman

Here is a macro I developed to deal with the SharePoint 2010 data download.  It took me some time to test as this appears to be working quite nicely.  Feel free to use this as is with no warranty implied.

 

/**************************************************************************************************************************
Read me:

SharePoint has a 1,000 record limit when serving data via rest in SharePoint 2010.  This limitation may be removed in future
situations, but this code is created so the it pulls the first 1,000 records then continues to pull the records matching the 
filter criteria until all the records matching the criteria are recieved.  

1. This macro uses the C:\temp folder so, if it is not in place it will be created.
2. When you run this macro on a list for the first time, you will need to look for the map file (in C:\temp) and modify it if 
   the table does not load all the data in the correct format that you are expecting, you can make adjustments.  Note: dates 
   will start and end with /Date( and )/ which getsstripped by the macro and converted to a SAS date.  Here is how a formatted date field should look like in the map file:

        {
          "NAME": "FieldNameInSharePoint",
          "TYPE": "CHARACTER",
          "PATH": "/root/d/results/FieldNameInSharePoint",  
          "CURRENT_LENGTH": 21
        },

	Note: you may have to go through a larger request then acutally needed to get the correct formats and/or
    manually put the type and current length in for fields that are KNOWN to be dates.  The TYPE AND CURRENT_LENGHT are
	typically the fields to check and adjust.

3. File created using SAS v9 Maintenance package 4.3.
**************************************************************************************************************************/

*Details of this macro are covered in the Download SharePoint 2010 data.sas file;
%macro createData(u=,list=,append=,convertdates=,usemymap=);
	%let dir = "c:\temp";
	*If the temp folder exists in C, use it, otherwise create it;
	%if %sysfunc(fileexist(&dir)) %then %do;       
	%end;                                          
	%else %do;
		dcreate("temp",'c:\');
   	%end;
	%let lname = &list;
	%if &append = No %then %do;
	   	%if %sysfunc(exist(&list)) %then %do;
			proc datasets lib=work nolist; delete &list; quit; run;
		  	%put NOTE: Deleted &list from WORK;
	   	%end;
	%end;
	*If the next variable is found below, come back up to process more records;
	*Jumping to this section allows you to avoid the table deletion call;
	%getmorerecords:

	*reset rowCount;
	%let rowCount = 0;
	%if &usemymap ne '' %then %do;
		%let map = "c:\temp\&usemymap._json.map";
	%let map = "c:\temp\&list._json.map";
	%end;                                 
	%else %do;
		%let map = "c:\temp\&list._json.map"; 
   	%end;

	filename minmap ↦
	*filename minmap "c:\temp\&list._json.map";
	*filename topics temp;

	filename topics "C:\temp\&list._json.txt" encoding="UTF-8";

	%put NOTE: calling data from &=u;
	*call the data from sharepoint; 
	proc http url=&u method="GET" out=topics AUTH_NEGOTIATE;
	headers
		"Accept"="application/json;odata=verbose" 
		"ContentType "="application/json;odata=verbose;charset=utf-8";
	run;

	%put NOTE: Assigning JSON and posts libref;
	/* Let the JSON engine do its thing */
	libname posts JSON fileref=topics map=minmap;* automap=replace;

	*If the map exists, use it, otherwise autocreate it;
	%if %sysfunc(fileexist(&map)) %then %do;       
		libname posts JSON fileref=topics map=minmap;* automap=replace;  
	%end;                                          
	%else %do;
		%put NOTE: Placing the map of loaded JSON formats in ↦
		libname posts JSON fileref=topics map=minmap automap=replace;  
   	%end;
	%if %sysfunc(libref(posts)) ne 0 %then %do;
		 %put ERROR: The SAS Data Library could;
		 %put ERROR- not be established;
		 %goto exit;
	%end;  

	%put NOTE: Checking for the __next link and creating the Next table in WORK;
	PROC SQL;
		CREATE TABLE NEXT AS SELECT __next as URL, "&list" || substr(__next,find(__next,'=',-length(__next))+1) as start from posts.D; 
	Quit;

	*If the table exists, then merge the results into the table, othewise create it.;
   	%if %sysfunc(exist(&list)) %then %do;
		%put NOTE: Merging new results with previous results;
		PROC SQL;
			create table list2 as select * from &list union select * from posts.D_results;
			create table &list as select * from list2;
		QUIT;
		run;
	%end;
	%else %do;
		%put NOTE: Creating &=list from posts.D_results;
		data &list; set posts.D_results; run;
   	%end;
	/*delete list2 if it exist, which it should in this case;*/
	%if %sysfunc(exist(list2)) %then %do;
		%put NOTE: Deleting list2 from WORK;
		proc datasets lib=work nolist; delete list2; quit;
	%end;
	/**Create a macro variable to hold the row count;*/
  	%local rowCount;
	%if %sysfunc(exist(Next)) %then %do;
	  	/*Query the record count from posts.D, then there are more records; */
		%put NOTE: Counting records in Next Table in WORK;
	  	proc sql noprint;
			select count(*) into :rowCount trimmed from Next; *&dsn;
			select URL into :preu trimmed from Next; *&dsn;
	 	quit;
		%if &rowCount eq 0 %then %do;
			%let u = ;
		%end;
		%else %do;
			%let u = "&preu";
		%end;

	%end;
	/*%else;
		%goto exit;
	%end;*/

  	%put NOTE: &=rowCount, Next link &=u;
	%put NOTE: Clearing libname references posts and JSON;
	libname posts clear; 
	/*delete list2 if it exist, which it should in this case;*/
	%put Releasing the references to minmap, topics, and posts so they can be reassigned on the next loop
	filename minmap clear;
	filename topics clear;
	libname posts clear;

	*If there is a row in posts.D then, set the url macro variable to the next set an the next table name as the skip token; 
	/*Start back up at the top to process another set of records;*/  	
	/*Replace the variables that are used to get and process data based on the next parameter;*/
	%if &rowCount > 0 %then %do;
		%if %sysfunc(exist(Next)) %then %do;
			%put NOTE: Deleting Next from WORK;
			proc datasets lib=work nolist; delete Next; quit;
		%end;
		%goto getmorerecords;
	%end;

	/**************************************************************************************************************************
	Look for all columns that are character 21 characters in lenght to see which columns need to be changed into a date format;
	**************************************************************************************************************************/

	%if &convertdates = Yes %then %do;
		%let ulist = %UPCASE(&list);
		proc sql noprint;
			create table tablesettings as select * from dictionary.columns where upcase(libname) = "WORK" and upcase(memname)="&ulist";
			create table nondates as select name,varnum,'nondate' as type from tablesettings where name not in ("ordinal_d","ordinal_results");
			create table dates as select name,varnum,'date' as type from tablesettings where type="char" and length=21;
			select count(*) into :datecount from dates;
		quit;
		%if  &datecount > 0 %then %do;
		  	proc sql noprint;
			  select distinct name into : name_list separated by ' ' from dates;
			quit;
		  	%local i next_name;
			%do i=1 %to %sysfunc(countw(&name_list));
			   %let next_name = %scan(&name_list, &i);
			   proc sql noprint;
			      select count(*) as count into :hasdates from &list where  &NEXT_NAME LIKE "/Date(%";/*"%Date(%";*/
			   quit;
				%if  &hasdates > 0 %then %do;
					*Delete the date field from the dates table;
					data nondates; set nondates; if name = "&NEXT_NAME" then delete; run; 
				%end;
	         	%else %do;
					/*%put &NEXT_NAME is NOT a date field;*/
					data dates; set dates; if name = "&NEXT_NAME" then delete; run; 
	            %end;
			%end;
			proc sql; create table columns as select * from nondates outer union corr select * from dates order by varnum; quit;
		%end;
	    %else %do;
			proc sql; create table columns as select * from nondates order by varnum; quit;
	    %end;
		proc sql noprint;
			select case
				when type = 'date' then trim("dhms('01jan1970'd,0,0,(input(tranwrd(tranwrd(" || trim(name) || ", '/Date(', ''),')/',''),14.))/1000 + TZONEOFF()) AS " || trim(name) || " format=datetime.")
		        else name
		        end as col into :col_list  separated by ", "
			from columns;
			create table &list.2 as
				select &col_list from &list;
			quit;
		quit;
		proc datasets lib=work nolist; delete Columns Dates Nondates Tablesettings; quit; run;
		proc datasets lib=work nolist; delete &list; quit; run;
		proc datasets lib=work nolist nowarn; change &list.2=&list; run; quit;	
	%end;


%exit:
%mend;


/**************************************************************************************************************************
How to use this macro:
%let url = your url to your list of interest;
Example: %createData(u=&url,list=rficfirm,append=Yes,usemymap=firm);
1. %createData() 
   This is now to call the macro (in general).  Follow instructions below to specify the settings.
2. u=&url 
   use the %let url = to reference the URL to download the sharepoint data (required)
3. list= 
   This is really a reference to the sharepoint list (required).  While it may
   be helpful to have the same name as the list in SharePoint, you can give it any name you like.
   Note: all references loaded in C:\temp will start with the value you use for list=
4. append=
   So, if you were to merge multiple queries, you may want to continue loading the data to the 
   same table.  However, if you want to continuously replace the table everytime you run the macro
   then you say append=No.  append=No will delete the table at the start of the macro so that
   if you were testing and you keep modifying your url, you are not constantly loading the data
   to the table causing duplicate records.
5. usemymap=
   If you are trying to download multiple different files of the same dataset, for example in one dataset there are 
   nearly 10,000 records that I need, but I want to keep them separate.  The have the same map, but I want the records 
   in different tables.  This allows you to give a different table name in 3 above but use the same map as 3 above. 
5. Note: SAS has been programmed to check all Characters fields lenght $21. to see if they contain /Date(
   If they do, then /Date( and )/ are removed and the fields are converted to SAS date format.  The 
   tabled referenced in 3. above is then reformatted in the original order that was downloaded so that 
   the user is able to get the table with the correct format irregardless of the UNIX date stamp provided
   by SharePoint. 

**************************************************************************************************************************/
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 154 views
  • 2 likes
  • 2 in conversation