In SAS, how to restructure data from Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

In SAS, how to restructure data from Excel

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


Accepted Solutions
Solution
‎12-17-2012 10:25 PM
Respected Advisor
Posts: 3,124

Re: In SAS, how to restructure data from Excel

: 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


All Replies
PROC Star
Posts: 7,363

Re: In SAS, how to restructure data from Excel

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=fSmiley Happy;

  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;

Super User
Posts: 10,500

Re: In SAS, how to restructure data from Excel

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.

Solution
‎12-17-2012 10:25 PM
Respected Advisor
Posts: 3,124

Re: In SAS, how to restructure data from Excel

: 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

Super User
Posts: 10,500

Re: In SAS, how to restructure data from Excel

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.

Occasional Contributor
Posts: 10

Re: In SAS, how to restructure data from Excel

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

Occasional Contributor
Posts: 10

Re: In SAS, how to restructure data from Excel

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

Kelly

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 464 views
  • 3 likes
  • 4 in conversation