Can't get infile to give more than one variable when trying to read json

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Can't get infile to give more than one variable when trying to read json

I am trying to read several variables from a json string in one datastep. I have been trying to use this as a guide http://support.sas.com/resources/papers/proceedings13/296-2013.pdf

 

I can get it working with only one variabel, but as soon as I try the same with more than one @'' input argument, all my variables are blank. Here is the full code I am using, which is more or less copy-pasted from the artivle. I cannot understand why it is not working for me. I am using Enterprise Guide 7.11 with (SAS 9.4.). Here is my full code:


filename json '\\myfolder\test.json';

data _null_;
   file json;
   input; 
   put _infile_;
   datalines4;
{
"completed_in":0.031,
"max_id":122078461840982016,
"max_id_str":"122078461840982016",
"next_page":"?page=2&max_id=122078461840982016&q=blue%20angels&rpp=5",
"page":1,
"query":"blue+angels",
"refresh_url":"?since_id=122078461840982016&q=blue%20angels",
"results":[
{
"created_at":"Thu, 06 Oct 2011 19:36:17 +0000",
"entities":{
"urls":[
{
"url":"http://t.co/L9JXJ2ee",
"expanded_url":"http://bit.ly/q9fyz9",
"display_url":"bit.ly/q9fyz9",
"indices":[
37,
57
]
}
]
},
"from_user":"SFist",
"from_user_id":14093707,
"from_user_id_str":"14093707",
"geo":null,
"id":122032448266698752,
"id_str":"122032448266698752",
"iso_language_code":"en",
"metadata":{
"recent_retweets":3,
"result_type":"popular"
},
"profile_image_url":"http://a3.twimg.com/profile_images/51584619/SFist07_normal.jpg",
"source":"<a href="http://Twitter.com/tweetbutton" rel="nofollow">Tweet
"text":"Reminder: Blue Angels practice today http://t.co/L9JXJ2ee",
"to_user_id":null,
"to_user_id_str":null
},
{
"created_at":"Thu, 06 Oct 2011 19:41:12 +0000",
"entities":{
46 },
"from_user":"masters212",
"from_user_id":2242041,
"from_user_id_str":"2242041",
"geo":null,
"id":122033683212419072,
"id_str":"122033683212419072",
"iso_language_code":"en",
"metadata":{
"recent_retweets":1,
"result_type":"popular"
},
"profile_image_url":"http://a3.twimg.com/profile_images/488532540/rachel25final_normal.jpg",
"source":"<a href="http://Twitter.com/">web</a>",
"text":"Starting to hear Blue Angels... Not such angels with all of the noise and carbon pollution.",
"to_user_id":null,
"to_user_id_str":null
;;;;

data datatest;
infile json lrecl = 32000 truncover scanover;
input @'"text":"' text $255. @'"from_user": "' from_user $255. @'"created_at":
"' created_at $255. ;
text = substr(text,1,index(text,'",')-2);
from_user = substr( from_user,1,index( from_user,'",')-2);
created_at = substr(created_at,1,index(created_at,'",')-2);
run;

 I get no warnings or errors in my log, but the following notes are printed:

text=Reminder: Blue Angels practice today http://t.co/L9JXJ2ee", from_user=  created_at=  _ERROR_=1 _N_=1
NOTE: 61 records were read from the infile JSON.
      The minimum record length was 80.
      The maximum record length was 160.
NOTE: SAS went to a new line when INPUT @'CHARACTER_STRING' scanned past the end of a line.
NOTE: The data set WORK.DATATEST has 0 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

If I only try yo get one variable out, I get two observations in my dataset:

data datatest;
infile json lrecl = 32000 truncover scanover;
input @'"text":"' text $255.;
text = substr(text,1,index(text,'",')-2);
run;

But as stated, with more input arguments, all variables are empty. 


Accepted Solutions
Solution
‎03-15-2016 09:38 AM
Respected Advisor
Posts: 3,782

Re: Can't get infile to give more than one variable when trying to read json

Did you see the LOSTCARD message quite telling.

 

You need to scan in order and your at target "from user" has and extra space so the program scans to the end of file. LOSTCARD.

 

See if this gives you what you expect.

 

input  @'"created_at":"' created_at $255. @'"from_user":"' from_user $255. @'"text":"' text $255.;

View solution in original post


All Replies
Solution
‎03-15-2016 09:38 AM
Respected Advisor
Posts: 3,782

Re: Can't get infile to give more than one variable when trying to read json

Did you see the LOSTCARD message quite telling.

 

You need to scan in order and your at target "from user" has and extra space so the program scans to the end of file. LOSTCARD.

 

See if this gives you what you expect.

 

input  @'"created_at":"' created_at $255. @'"from_user":"' from_user $255. @'"text":"' text $255.;
Contributor
Posts: 22

Re: Can't get infile to give more than one variable when trying to read json

I did see it, but it didn't really tell me anything. LOSTCARD is not a term I have ever come across before. And since it wasn't an error, not even a warning, I didn't think much of it. So the example from the paper, which clearly doesn't put the items in order, is flat out wrong? 

 

I assumed the exmple had to be right, so I was tearing my hair out when copy-pasting the whole thing didn't even work. Lesseon learnt I guess! Never assume something will work just because it is found in a paper on support.sas.com.

 

Thank you for the help, I really had no idea where to go next when even a copy of an example didn't work. I was beginning to think this might be an issue with my SAS installation

Respected Advisor
Posts: 3,782

Re: Can't get infile to give more than one variable when trying to read json

The LOSTCARD message in your program could be interpreted as "unexpected end of file".  Or "SAS scaned but did not find".

 

SAS executed the LOST CARD statement when your program scanned to EOF.  You can read about it here. 

http://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n0hesbzvvszy1dn...

 

 

Not sure how the program in the paper worked.  Maybe with different data it would work.

Contributor
Posts: 22

Re: Can't get infile to give more than one variable when trying to read json

The wierd thing is I copied the data as stated in the article. As far as I can tell, the program could not have worked if the data looked as printed in the article

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 368 views
  • 0 likes
  • 2 in conversation