BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
raul0002
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
Yavuz
Quartz | Level 8
Did you tried txt import wizard.
ballardw
Super User

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.

raul0002
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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