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

Hi,

I have data that I copied and pasted from a website into an Excel spreadsheet in the attached file. Every 3 rows are actually 1 observation. For eg, the first row is: Brandname of the camera (Canon Powershot G12), the second row is the current price of that camera ($469.99) and the third row is the discount amount on that camera price ($30.00). Then there is blank row and then the next camera, and so on.

I need to read this data into SAS and create the 3 variables: Brandnamemodel, Price, discountamount. How could this be done?

I could not find any help with this online. Thank you so much and look forward to hearing from you.

Kelly

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

: I like your approach. To avoid missing the last record (as if saving it as is, the last empty line will be eliminated), and no need for junk: variables:

data want;

infile 'c:\temp\sampledatanov21.csv' truncover end=last;

informat brandname $50. price discount dollar8.2;

input brandname $&;

input @5 price;

input  @5 discount;

if not last then input;

run;

Haikuo

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

Here is one way:

PROC IMPORT OUT= WORK.have

            DATAFILE= "C:\art\sampledatanov21.xls"

            DBMS=EXCEL REPLACE;

     GETNAMES=no;

     MIXED=NO;

RUN;

data want (drop=f:);

  set have;

  retain Brandnamemodel Price;

  format Price Discount dollar8.2;

  f2=find(f1,"$");

  select (mod(_n_,4));

    when (1) Brandnamemodel=strip(f1);

    when (2) Price=input(substr(f1,f2+1),comma32.);

    when (3) do;

               Discount=input(substr(f1,f2+1),comma32.);

               output;

             end;

    otherwise;

  end;

run;

ballardw
Super User

The first thing I would do is make sure the data pattern is actually the same. You have a different record format for

 

Canon A810 Red + 500 prints Web

If you fix that then 1) save as a CSV file as import from Excel is going to take somewhat more complicated code and

then

 

data want;

infile 'your path to saved file\sampledatanov21(1).csv' lrecl=256 pad missover ;

informat brandname $50. price discount dollar8.2 junk1 junk2 $4.;

input brandname $ 1-50;

input junk1 $ 1-4 price;

input junk2 $ 1-4 discount;

input;

drop junk:;

run;


Worked for me.

Haikuo
Onyx | Level 15

: I like your approach. To avoid missing the last record (as if saving it as is, the last empty line will be eliminated), and no need for junk: variables:

data want;

infile 'c:\temp\sampledatanov21.csv' truncover end=last;

informat brandname $50. price discount dollar8.2;

input brandname $&;

input @5 price;

input  @5 discount;

if not last then input;

run;

Haikuo

ballardw
Super User

The column pointers are better when I know what's going on. I wasn't sure if we might get something else in case the data does't follow the pattern in the first few lines of data. That's a habit I got into because I had way too many file layout descriptions not quite match the actual data received. The I don't drop the junk variables for diagnostics.

kellykishan
Calcite | Level 5

Hi Hai.kuo,

Thank you for the modified code. It worked perfectly and it was simple as well.

Since I am new to SAS could you please explain what $& means? I could not find it anywhere online.

Many thanks,

Kelly

kellykishan
Calcite | Level 5

Edward, thank you also for your help and input. Greatly appreciated!

Kelly

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
  • 6 replies
  • 948 views
  • 3 likes
  • 4 in conversation