In SAS, how to restructure data from Excel

[kellykishan]

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

[art297]

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]

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 · Accepted solution]

: 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]

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]

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]

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

Kelly