topic In SAS, how to restructure data from Excel in SAS Programming

In SAS, how to restructure data from Excel

kellykishan — Mon, 17 Dec 2012 22:28:05 GMT

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

Re: In SAS, how to restructure data from Excel

art297 — Mon, 17 Dec 2012 22:58:03 GMT

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;

Re: In SAS, how to restructure data from Excel

ballardw — Mon, 17 Dec 2012 23:04:31 GMT

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.

Re: In SAS, how to restructure data from Excel

Haikuo — Tue, 18 Dec 2012 03:25:33 GMT

: 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

Re: In SAS, how to restructure data from Excel

ballardw — Tue, 18 Dec 2012 15:09:43 GMT

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.

Re: In SAS, how to restructure data from Excel

kellykishan — Tue, 18 Dec 2012 21:12:46 GMT

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

Re: In SAS, how to restructure data from Excel

kellykishan — Tue, 18 Dec 2012 21:14:31 GMT

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

Kelly