混合数据模型：常数和变量都放在common里面；
变截距模型：变量输入common里面；
变系数模型：c和变量都要放在cross—section specific里面。

http://www.ejxjy.com/ http://www.ejxjy.com/  师大培训中心 63340811邮箱

https://www.enetedu.com/ https://www.enetedu.com/ jung@163邮箱

6.1. Using the Import facility 
6.2. Using PROC ACCESS 
6.3. Using Dynamic Data Exchange (DDE)

INFILE "C:\sasfiles\testfile.csv" DLM='09'x DSD LRECL=1024 TRUNCOVER FIRSTOBS=2;

DATA A; INFILE "C:\ sasfiles\testfile.csv"; INPUT VAR1 VAR2 VAR3; RUN;

%macro o2xl(xlname,sdata);

 ods tagsets.ExcelXP  file="d:\name.xls"  options(sheet_name="&name.") style=XLStatistical;

 proc print data=&data. noobs label;

 run;

%mend;

* style 需要另外设置, label 表示在excel输出sas标签;

   ……

ods tagsets.excelxp close;

单个数据表输出：

可以使用系统内置的tagsets.excelxp来分worksheet输出 

ods  tagsets.excelxp  file="d:\test.xls" options(sheet_name="print") style=analysis;
proc print data=sashelp.class;run;

ods  tagsets.excelxp  options(sheet_name="freq");
proc freq data=sashelp.class;
     tables sex;
run;

ods tagsets.excelxp close;

SAS Learning Module

Inputting data into SAS

This module will show how to input raw data into SAS, showing how to read instream data and external raw data files using some common raw data formats.  Section 3 shows how to read external raw data files on a PC, UNIX/AIX, and Macintosh, while sections 4-6 give examples showing how to read the external raw data files on a PC, however these examples are easily converted to work on UNIX/AIX or a Macintosh based on the examples shown in section 3.

1. Reading free formatted data instream

One of the most common ways to read data into SAS is by reading the data instream in a data step - that is, by typing the data directly into the syntax of your SAS program. This approach is good for relatively small datasets. Spaces are usually used to "delimit" (or separate) free formatted data. For example:

 

DATA cars1;

 INPUT make $ model $ mpg weight price;

CARDS;

AMC Concord 22 2930 4099

AMC Pacer   17 3350 4749

AMC Spirit  22 2640 3799

Buick Century 20 3250 4816

Buick Electra 15 4080 7827

;

RUN; 

After reading in the data with a data step, it is usually a good idea to print the first few cases of your dataset to check that things were read correctly.

title "cars1 data";

PROC PRINT DATA=cars1(obs=5);

RUN; 

Here is the output produced by the proc print statement above.

cars1 data            

OBS   MAKE      MODEL     MPG    WEIGHT    PRICE

1     AMC      Concord     22     2930      4099

2     AMC      Pacer       17     3350      4749

3     AMC      Spirit      22     2640      3799

4     Buick    Century     20     3250      4816

5     Buick    Electra     15     4080      7827

2. Reading fixed formatted data instream

Fixed formatted data can also be read instream. Usually, because there are no delimiters (such as spaces, commas, or tabs) to separate fixed formatted data, column definitions are required for every variable in the dataset. That is, you need to provide the beginning and ending column numbers for each variable. This also requires the data to be in the same columns for each case. For example, if we rearrange the cars data from above, we can read it as fixed formatted data:

DATA cars2;

  INPUT make $ 1-5 model $ 6-12 mpg 13-14 weight 15-18 price 19-22;

CARDS;

AMC  Concord2229304099

AMC  Pacer  1733504749

AMC  Spirit 2226403799

BuickCentury2032504816

BuickElectra1540807827

;

RUN;

TITLE "cars2 data";

PROC PRINT DATA=cars2(obs=5);

RUN; 

The benefit of fixed formatted data is that you can fit more information on a line when you do not use delimiters such as spaces or commas. 

Here is the output produced by the proc print statement above.

cars2 data            

OBS    MAKE      MODEL     MPG    WEIGHT    PRICE

1     AMC      Concord     22     2930      4099

2     AMC      Pacer       17     3350      4749

3     AMC      Spirit      22     2640      3799

4     Buick    Century     20     3250      4816

5     Buick    Electra     15     4080      7827

3. Reading fixed formatted data from an external file

Suppose you are using a PC and you have a file named cars3.dat, that is stored in the c:\carsdata directory of your computer.  Here's what the data in the file cars3.dat look like:

AMC  Concord2229304099

AMC  Pacer  1733504749

AMC  Spirit 2226403799

BuickCentury2032504816

BuickElectra1540807827 

To read the file cars3.dat, use the following syntax.

 DATA cars3;

  INFILE "c:\carsdata\cars3.dat";

  INPUT make $ 1-5 model $ 6-12 mpg 13-14 weight 15-18 price 19-22;

RUN;

TITLE "cars3 data";

PROC PRINT DATA=cars3(obs=5);

RUN; 

Here is the output produced by the proc print statement above.

cars3 data            

OBS    MAKE      MODEL     MPG    WEIGHT    PRICE

1     AMC      Concord     22     2930      4099

2     AMC      Pacer       17     3350      4749

3     AMC      Spirit      22     2640      3799

4     Buick    Century     20     3250      4816

5     Buick    Electra     15     4080      7827

Suppose you were working on UNIX.  The UNIX version of this program, assuming the file cars3.dat is located in the directory ~/carsdata, would use the syntax shown below.  (Note that the "~" in the UNIX pathname above refers to the user's HOME directory. Hence, the directory called carsdata that is located in the users HOME directory.)

DATA cars3;

  INFILE "~/carsdata/cars3.dat";

  INPUT make $ 1-5 model $ 6-12 mpg 13-14 weight 15-18 price 19-22;

RUN;

TITLE "cars3 data";

PROC PRINT DATA=cars3(obs=5);

RUN; 

Likewise, suppose you were working on a Macintosh.  The Macintosh version of this program, assuming cars3.dat is located on your hard drive (called Hard Drive) in a folder called carsdata would look like this.

DATA cars3;

  INFILE 'Hard Drive:carsdata:cars3.dat';

  INPUT make $ 1-5 model $ 6-12 mpg 13-14 weight 15-18 price 19-22;

RUN;

TITLE "cars3 data";

PROC PRINT DATA=cars3(OBS=5);

RUN; 

In examples 4, 5 and 6 below, you can change the infile statement as these examples have shown to make the programs appropriate for UNIX or for the Macintosh.

4. Reading free formatted (space delimited) data from an external file

Free formatted data that is space delimited can also be read from an external file. For example, suppose you have a space delimited file named cars4.dat, that is stored in the c:\carsdata directory of your computer. 

Here's what the data in the file cars4.dat look like:

AMC Concord 22 2930 4099

AMC Pacer   17 3350 4749

AMC Spirit  22 2640 3799

Buick Century 20 3250 4816

Buick Electra 15 4080 7827 

To read the data from cars4.dat into SAS, use the following syntax:

DATA cars4;

  INFILE "c:\carsdata\cars4.dat";

  INPUT make $ model $ mpg weight price;

RUN;

TITLE "cars4 data";

PROC PRINT DATA=cars4(OBS=5);

RUN; 

Here is the output produced by the proc print statement above.

cars4 data            

OBS    MAKE      MODEL     MPG    WEIGHT    PRICE

1     AMC      Concord     22     2930      4099

2     AMC      Pacer       17     3350      4749

3     AMC      Spirit      22     2640      3799

4     Buick    Century     20     3250      4816

5     Buick    Electra     15     4080      7827 

5. Reading free formatted (comma delimited) data from an external file

Free formatted data that is comma delimited can also be read from an external file. For example, suppose you have a comma delimited file named cars5.dat, that is stored in the c:\carsdata directory of your computer. 

Here's what the data in the file cars5.dat look like:

AMC,Concord,22,2930,4099

AMC,Pacer,17,3350,4749

AMC,Spirit,22,2640,3799

Buick,Century,20,3250,4816

Buick,Electra,15,4080,7827 

To read the data from cars5.dat into SAS, use the following syntax:

DATA cars5;

  INFILE "c:\carsdata\cars5.dat" delimiter=',';

  INPUT make $ model $ mpg weight price;

RUN;

TITLE "cars5 data";

PROC PRINT DATA=cars5(OBS=5);

RUN; 

Here is the output produced by the proc print statement above.

cars5 data            

OBS    MAKE      MODEL     MPG    WEIGHT    PRICE

1     AMC      Concord     22     2930      4099

2     AMC      Pacer       17     3350      4749

3     AMC      Spirit      22     2640      3799

4     Buick    Century     20     3250      4816

5     Buick    Electra     15     4080      7827 

6. Reading free formatted (tab delimited) data from an external file

Free formatted data that is TAB delimited can also be read from an external file. For example, suppose you have a tab delimited file named cars6.dat, that is stored in the c:\carsdata directory of your computer. 

Here's what the data in the file cars6.dat look like:

AMC Concord 22 2930 4099

AMC Pacer 17 3350 4749

AMC Spirit 22 2640 3799

Buick Century 20 3250 4816

Buick Electra 15 4080 7827 

To read the data from cars6.dat into SAS, use the following syntax:

DATA cars6;

  INFILE "c:\carsdata\cars6.dat" DELIMITER='09'x;

  INPUT make $ model $ mpg weight price;

RUN;

TITLE "cars6 data";

PROC PRINT DATA=cars6(OBS=5);

RUN; 

Here is the output produced by the proc print statement above.

cars6 data            

OBS    MAKE      MODEL     MPG    WEIGHT    PRICE

1     AMC      Concord     22     2930      4099

2     AMC      Pacer       17     3350      4749

3     AMC      Spirit      22     2640      3799

4     Buick    Century     20     3250      4816

5     Buick    Electra     15     4080      7827

7. Problems to look out for

If you read a file that is wider than 80 columns, you may need to use the lrecl= parameter on the infile statement.

8. For more information

For more detailed information on reading raw data into SAS, see Reading data into SAS in the SAS Library.

To learn how to create permanent SAS system files, see the SAS Learning Module on Reading and writing SAS system files.

 

For information on creating and recoding variables once you have entered your data, see the SAS Learning Module on Creating and recoding variables.

Section 2.4

First program

* Read internal data into SAS data set uspresidents; 

DATA uspresidents;    

INPUT President $ Party $ Number;

   DATALINES;

Adams     F  2

Lincoln   R 16

Grant     R 18

Kennedy   D 35

   ;

RUN;

Second program

* Read data from external file into SAS data set; 

DATA uspresidents;

   INFILE 'c:\MyRawData\President.dat';

   INPUT President $ Party $ Number;

RUN;

Section 2.5

 

* Create a SAS data set named toads;

* Read the data file ToadJump.dat using list input;

DATA toads;

   INFILE 'c:\MyRawData\ToadJump.dat';

   INPUT ToadName $ Weight Jump1 Jump2 Jump3;

RUN;

* Print the data to make sure the file was read correctly;

PROC PRINT DATA = toads;

   TITLE 'SAS Data Set Toads';

RUN;

Section 2.6

 

* Create a SAS data set named sales;

* Read the data file OnionRing.dat using column input;

DATA sales;

   INFILE 'c:\MyRawData\OnionRing.dat';

   INPUT VisitingTeam $ 1-20 ConcessionSales 21-24 BleacherSales 25-28

         OurHits 29-31 TheirHits 32-34 OurRuns 35-37 TheirRuns 38-40;

RUN;

* Print the data to make sure the file was read correctly;

PROC PRINT DATA = sales;

   TITLE 'SAS Data Set Sales';

RUN;

Section 2.7

 

* Create a SAS data set named contest;

* Read the file Pumpkin.dat using formatted input;

DATA contest;

   INFILE 'c:\MyRawData\Pumpkin.dat';

   INPUT Name $16. Age 3. +1 Type $1. +1 Date MMDDYY10.

         (Score1 Score2 Score3 Score4 Score5) (4.1);

RUN;

* Print the data set to make sure the file was read correctly;

PROC PRINT DATA = contest;

   TITLE 'Pumpkin Carving Contest';

RUN;

Section 2.9

First Program

 

* Create a SAS data set named nationalparks;

* Read a data file NatPark.dat mixing input styles;

DATA nationalparks;

   INFILE 'c:\MyRawData\NatPark.dat';

   INPUT ParkName $ 1-22 State $ Year @40 Acreage COMMA9.;

RUN;

PROC PRINT DATA = nationalparks;

   TITLE 'Selected National Parks';

RUN;

Second Program

* Create a SAS data set named nationalparks;

* Read a data file NatPark.dat mixing input styles;

DATA nationalparks;

   INFILE 'c:\MyRawData\NatPark.dat';

   INPUT ParkName $ 1-22 State $ Year Acreage COMMA9.;

RUN;

PROC PRINT DATA = nationalparks;

   TITLE 'Selected National Parks';

RUN;

Section 2.10

 

DATA weblogs;

  INFILE 'c:\MyWebLogs\dogweblogs.txt';

  INPUT @'[' AccessDate DATE11. @'GET' File :$20.;

RUN;

PROC PRINT DATA = weblogs;

  TITLE 'Dog Care Web Logs';

RUN;

Section 2.11

 

* Create a SAS data set named highlow;

* Read the data file using line pointers;

DATA highlow;

   INFILE 'c:\MyRawData\Temperature.dat';

   INPUT City $ State $ 

         / NormalHigh NormalLow

         #3 RecordHigh RecordLow;

RUN;

PROC PRINT DATA = highlow;

   TITLE 'High and Low Temperatures for July';

RUN;

Section 2.12

 

* Input more than one observation from each record;

DATA rainfall;

   INFILE 'c:\MyRawData\Precipitation.dat';

   INPUT City $ State $ NormalRain MeanDaysRain @@;

RUN;

PROC PRINT DATA = rainfall;

   TITLE 'Normal Total Precipitation and';

   TITLE2 'Mean Days with Precipitation for July';

RUN;

Section 2.13

 

* Use a trailing @, then delete surface streets;

DATA freeways;

   INFILE 'c:\MyRawData\Traffic.dat';

   INPUT Type $ @;

   IF Type = 'surface' THEN DELETE;

   INPUT Name $ 9-38 AMTraffic PMTraffic;

RUN;

PROC PRINT DATA = freeways;

   TITLE 'Traffic for Freeways';

RUN;

Section 2.14

First Program

DATA icecream;

   INFILE 'c:\MyRawData\IceCreamSales.dat' FIRSTOBS = 3;

   INPUT Flavor $ 1-9 Location BoxesSold;

RUN;

 Second Program 

DATA icecream;

   INFILE 'c:\MyRawData\IceCreamSales2.dat' FIRSTOBS = 3 OBS=5;

   INPUT Flavor $ 1-9 Location BoxesSold;

RUN;

 Third Program 

DATA class102;

   INFILE 'c:\MyRawData\AllScores.dat' MISSOVER;

   INPUT Name $ Test1 Test2 Test3 Test4 Test5;

RUN; 

 Fourth Program 

DATA homeaddress;

   INFILE 'c:\MyRawData\Address.dat' TRUNCOVER;

   INPUT Name $ 1-15 Number 16-19 Street $ 22-37;

RUN;

Section 2.15

 First Program 

DATA reading;

   INFILE 'c:\MyRawData\Books.dat' DLM = ',';

   INPUT Name $ Week1 Week2 Week3 Week4 Week5;

RUN;

 Third Program (second program intentionally omitted)

DATA music;

   INFILE 'c:\MyRawData\Bands.csv' DLM = ',' DSD MISSOVER;

   INPUT BandName :$30. GigDate :MMDDYY10. EightPM NinePM TenPM ElevenPM;

RUN;

PROC PRINT DATA = music;

   TITLE 'Customers at Each Gig';

RUN;

Section 2.16 

 

PROC IMPORT DATAFILE ='c:\MyRawData\Bands2.csv' OUT = music REPLACE;

RUN;

PROC PRINT DATA = music;    

   TITLE 'Customers at Each Gig'; RUN;

Section 2.17 

 

(data must be read from a spreadsheet) 

PROC IMPORT DATAFILE = 'c:\MyExcelFiles\OnionRing.xls' DBMS=XLS OUT = sales;

RUN;

PROC PRINT DATA = sales;

   TITLE 'SAS Data Set Read From Excel File'; RUN;

Section 2.18 

 First Program (data must be read from a spreadsheet) 

* Read an Excel spreadsheet using DDE;

FILENAME baseball DDE 'CLIPBOARD';

DATA sales;

  INFILE baseball NOTAB DLM='09'x DSD MISSOVER;

  LENGTH VisitingTeam $ 20;

  INPUT VisitingTeam CSales BSales OurHits TheirHits OurRuns TheirRuns;

RUN;

 Second Program (data must be read from a spreadsheet, must replace DDE triplet with DDE triplet for your own files

* Read an Excel spreadsheet using DDE;

OPTIONS NOXSYNC NOXWAIT;

X '"C:\MyFiles\BaseBall.xls"';

FILENAME baseball DDE 'Excel|C:\MyFiles\[BaseBall.xls]sheet1!R2C1:R5C7';

DATA sales;

  INFILE baseball NOTAB DLM='09'x DSD MISSOVER;

  LENGTH VisitingTeam $ 20;

  INPUT VisitingTeam CSales BSales OurHits TheirHits OurRuns TheirRuns;

RUN;

Section 2.19 

 First Program 

DATA distance; 

   Miles = 26.22;

   Kilometers = 1.61 * Miles;

RUN;

PROC PRINT DATA = distance;

RUN;

 Second Program 

DATA Bikes.distance; 

   Miles = 26.22;

   Kilometers = 1.61 * Miles;

RUN;

PROC PRINT DATA = Bikes.distance;

RUN;

Section 2.20 

 First Program 

LIBNAME plants 'c:\MySASLib';

DATA plants.magnolia;

   INFILE 'c:\MyRawData\Mag.dat';

   INPUT ScientificName $ 1-14 CommonName $ 16-32 MaximumHeight

      AgeBloom Type $ Color $;

RUN;

 Second Program 

LIBNAME example 'c:\MySASLib';

PROC PRINT DATA = example.magnolia;

   TITLE 'Magnolias';

RUN;

Section 2.21

 First Program 

DATA 'c:\MySASLib\magnolia';

   INFILE 'c:\MyRawData\Mag.dat';

   INPUT ScientificName $ 1-14 CommonName $ 16-32 MaximumHeight

      AgeBloom Type $ Color $;

RUN;

 Second Program

 

PROC PRINT DATA = 'c:\MySASLib\magnolia';

   TITLE 'Magnolias';

RUN;

Section 2.22 

 

DATA funnies (LABEL = 'Comics Character Data');

   INPUT Id Name $ Height Weight DoB MMDDYY8. @@;

   LABEL Id  = 'Identification no.'

      Height = 'Height in inches'

      Weight = 'Weight in pounds'

      DoB    = 'Date of birth';

   INFORMAT DoB MMDDYY8.;

   FORMAT DoB WORDDATE18.;

   DATALINES;

53      Susie 42 41 07-11-81 

54      Charlie 46 55 10-26-54

55      Calvin 40 35 01-10-81 

56      Lucy 46 52 01-13-55

   ;

* Use PROC CONTENTS to describe data set funnies;

PROC CONTENTS DATA = funnies;

RUN;