Date:         Sat, 27 Sep 2003 15:00:29 GMT
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: How to convert this excel file to SAS

Thomas,

One way of doing it, if you have "SAS/ACCESS to ODBC", would be as follows:
--------
PROC IMPORT OUT= WORK.one
            DATAFILE= "C:\stockdata.xls"
            DBMS=EXCEL2000 REPLACE;
     RANGE="a1:a1";
     GETNAMES=NO;
RUN;
data _null_;
  set one;
  CALL SYMPUT('fundname', PUT(f1,$30.));
run;
PROC IMPORT OUT= WORK.two
            DATAFILE= "C:\stockdata.xls"
            DBMS=EXCEL2000 REPLACE;
     RANGE="a7:c57";
     GETNAMES=YES;
RUN;
data three;
  set two;
  Mutual_Fund_Name="&fundname";
run;
-------
Art
-------
"Thomas ZHOU" <zhoux@bschool.unc.edu> wrote in message
news:2c8d6e3e.0309261525.9a3d5d4@posting.google.com...
> I have a data set kept in an Excel file. It is about mutual funds'
> portfolio holding, i.e. what security, how much were held by each
> fund. I am a new comer here so do not know how to attach the file. But
> it looks like this:
>
>         Fidelity Energy A
>          Portfolio Holding
>          as  of mm/dd/yy
>
> Total Net Assets $MM:xxx         No. of Securities: 56
>
> Shares #         Share-Name     Share-value($000)
> 114000           Exxon          7552.5
> 114800           Texaco         7275.3
> ... another 54 lines
>
> Then there is another fund, with the same format.
>
> My question is, how can I change it to a format like this
>
> Mutual-Fund-name     Share #   Share-Name   Share-Value
> 56 repetition here
>
> after 56 lines, a new fund name will appear
>
>
> I guess that I did not put myself clearly, for those who may be able
> to help, please let me know and I will attach my existing excel file
> to you and tell you what kind of format I want.
>
> Thanks a lot.
>
> Thomas