DATA Step, Macro, Functions and more

How to convert this review data in text file to sas dataset?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to convert this review data in text file to sas dataset?

[ Edited ]

I have been using sas support for getting my queries solved, but this is the first time I'm posting a question here. Currently I'm having a review dataset in text file in the following format:

 

product/productId: B00032K32A
product/title: Cruiser Accessories 21330 Slim Rim, Chrome License Plate Frame
product/price: 4.99
review/userId: A2O41UFL8HAQWV review/profileName: Nick Nefsik
review/helpfulness: 4/4
review/score: 5.0review/time: 1239667200review/summary: It's slim, alright! review/text: Similar to another review, I also found that this frame is more of a overlay to a license plate (sits on top of the plate), as opposed to securing the plate underneath it, if that makes sense.It *just* covers the edges of my AZ plate, which is fine, but I sure wouldn't want it to be any smaller around its outside perimeter. I also ordered the chrome covers for the screws (Cruiser Accessories 82030 Screw Covers, Chrome) that I was already using, and, altogether, it looks great, and is exactly the look I was going for.

product
/productId: B00032K32A product/title: Cruiser Accessories 21330 Slim Rim, Chrome License Plate Frame
product/price: 4.99
review/userId: A3V7H58BH72AYT review/profileName: Illustratedman
review/helpfulness: 6/7
review/score: 5.0
review/time: 1199145600
review/summary: Nice...
review/text: I first purchased these for my new 2008 Honda Accord EX-L to complement the chrome on the car and though they looked nice I eventually ordered the 20130 version of the Cruiser chrome frame for the wider border.

 

I want to convert this file to SAS dataset. Can anyone please help me how to proceed with the same?


Accepted Solutions
Solution
‎04-07-2017 05:44 PM
Super User
Posts: 10,466

Re: How to convert this review data in text file to sas dataset?

Because you did not attach example data in the form of a text file I have had to make some guesses but this may get you started.

I assume that each of your fields appears on it's own row in the data (the forum may have reformatted your example data in a couple places), there is a blank line between records and the last record has a blank line afterwards.

Your infile statement would want to read the source file, should include an LRECL setting long enough to read the longest expected field (your TEXT) plus its row label. The informat for TEXT should likely be longer I just picked 500.

data example;
   infile datalines  truncover n=10  ;
   input  
      @'productId: ' ProductId $12. 
     /@'title: ' title $75.
     /@'price: ' Price best8.
     /@'userId: ' userId  $20.
     /@'profileName: ' profileName $20.
     /@'helpfulness: ' helpfulness $5.
     /@'score: ' score  best8.
     /@'time: ' time  $12.
     /@'summary: ' summary $80.
     /@'text: ' text  $500.
     /
   ;
datalines;
product/productId: B00032K32A
product/title: Cruiser Accessories 21330 Slim Rim, Chrome License Plate Frame
product/price: 4.99
review/userId: A2O41UFL8HAQWV
review/profileName: Nick Nefsik
review/helpfulness: 4/4
review/score: 5.0
review/time: 1239667200
review/summary: It's slim, alright!
review/text: Similar to another review, I also found that this frame is more of a overlay to a   license plate (sits on top of the plate), as opposed to securing the plate underneath it, if that makes sense.It *just* covers the edges of my AZ plate, which is fine, but I sure wouldn't want it to be any smaller around its outside perimeter. I also ordered the chrome covers for the screws (Cruiser Accessories 82030 Screw Covers, Chrome) that I was already using, and, altogether, it looks great, and is exactly the look I was going for.

product/productId: B00032K32A
product/title: Cruiser Accessories 21330 Slim Rim, Chrome License Plate Frame
product/price: 4.99
review/userId: A3V7H58BH72AYT
review/profileName: Illustratedman
review/helpfulness: 6/7
review/score: 5.0
review/time: 1199145600
review/summary: Nice...
review/text: I first purchased these for my new 2008 Honda Accord EX-L to complement the 

;
run;

What is going on: the Infile parameter N is used to indicate how many rows of the data file are available to the read buffer. Truncover says is I ask to read more characters then are on the field then just read the characters there.

 

The @ is a column indicator. When coupled with a string as in @'productId: ' it tells SAS to start looking for data after the last character in the string. The formats tell SAS how to read each variable and since the format appears on the input statement it reads including spaces or special characters all as one value.

The / says "read from next line.

The last / is to move the input buffer to the blank line so the next input starts on the following line for the new product id.

 

IF any of these records do not have all of the fields then you will have to do a bunch of extra code to determine which field to read and when to output the data to the set.

View solution in original post


All Replies
Contributor
Posts: 43

Re: How to convert this review data in text file to sas dataset?

Did you tried txt import wizard.
Solution
‎04-07-2017 05:44 PM
Super User
Posts: 10,466

Re: How to convert this review data in text file to sas dataset?

Because you did not attach example data in the form of a text file I have had to make some guesses but this may get you started.

I assume that each of your fields appears on it's own row in the data (the forum may have reformatted your example data in a couple places), there is a blank line between records and the last record has a blank line afterwards.

Your infile statement would want to read the source file, should include an LRECL setting long enough to read the longest expected field (your TEXT) plus its row label. The informat for TEXT should likely be longer I just picked 500.

data example;
   infile datalines  truncover n=10  ;
   input  
      @'productId: ' ProductId $12. 
     /@'title: ' title $75.
     /@'price: ' Price best8.
     /@'userId: ' userId  $20.
     /@'profileName: ' profileName $20.
     /@'helpfulness: ' helpfulness $5.
     /@'score: ' score  best8.
     /@'time: ' time  $12.
     /@'summary: ' summary $80.
     /@'text: ' text  $500.
     /
   ;
datalines;
product/productId: B00032K32A
product/title: Cruiser Accessories 21330 Slim Rim, Chrome License Plate Frame
product/price: 4.99
review/userId: A2O41UFL8HAQWV
review/profileName: Nick Nefsik
review/helpfulness: 4/4
review/score: 5.0
review/time: 1239667200
review/summary: It's slim, alright!
review/text: Similar to another review, I also found that this frame is more of a overlay to a   license plate (sits on top of the plate), as opposed to securing the plate underneath it, if that makes sense.It *just* covers the edges of my AZ plate, which is fine, but I sure wouldn't want it to be any smaller around its outside perimeter. I also ordered the chrome covers for the screws (Cruiser Accessories 82030 Screw Covers, Chrome) that I was already using, and, altogether, it looks great, and is exactly the look I was going for.

product/productId: B00032K32A
product/title: Cruiser Accessories 21330 Slim Rim, Chrome License Plate Frame
product/price: 4.99
review/userId: A3V7H58BH72AYT
review/profileName: Illustratedman
review/helpfulness: 6/7
review/score: 5.0
review/time: 1199145600
review/summary: Nice...
review/text: I first purchased these for my new 2008 Honda Accord EX-L to complement the 

;
run;

What is going on: the Infile parameter N is used to indicate how many rows of the data file are available to the read buffer. Truncover says is I ask to read more characters then are on the field then just read the characters there.

 

The @ is a column indicator. When coupled with a string as in @'productId: ' it tells SAS to start looking for data after the last character in the string. The formats tell SAS how to read each variable and since the format appears on the input statement it reads including spaces or special characters all as one value.

The / says "read from next line.

The last / is to move the input buffer to the blank line so the next input starts on the following line for the new product id.

 

IF any of these records do not have all of the fields then you will have to do a bunch of extra code to determine which field to read and when to output the data to the set.

New Contributor
Posts: 2

Re: How to convert this review data in text file to sas dataset?

Thank you so much for your reply. The above worked perfectly for me Smiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 92 views
  • 0 likes
  • 3 in conversation