Date:         Fri, 4 Nov 2005 13:06:31 -0500
Reply-To:     "Michael S. Zdeb" <msz03@HEALTH.STATE.NY.US>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Michael S. Zdeb" <msz03@HEALTH.STATE.NY.US>
Subject:      Re: Code to read a text file
Comments: To: "Tonkovich, Mike" <Mike.Tonkovich@DNR.STATE.OH.US>
In-Reply-To:  <71D0E5BC413F4A468D00ECE85D52193A81B2CF@NRMX1.dnr.state.oh.us>
Content-type: text/plain; charset=US-ASCII

Hi...one idea...use list input to read the data as a comma-delimited, then
use arrays and a loop to rearrange and output the data...

data test;
infile datalines dsd;

array yr(6);
array f(6);
array y(6);
array s(6);
array a(6);

input
county : $20. yr1-yr6 /
dummy $       f1-f6   /
dummy $       y1-y6   /
dummy $       s1-s6   /
dummy $       a1-a6
;

do j=1 to 6;
   fawn = f(j);
   yearling = y(j);
   subadult = s(j);
   adult = a(j);
   year = yr(j);
   output;
end;

keep county fawn yearling subadult adult year;

datalines;
Adams,1977,1978,1979,1980,1981,1982
Fawns,2,1,5,10,33,34
Yearlings,60,63,72,94,119,140
Subadults,24,22,23,36,42,49
Adults,13,13,14,15,15,22
;
run;

proc print data=test;
run;

gave me...

Obs    county    fawn    yearling    subadult    adult    year

 1     Adams       2         60         24         13     1977
 2     Adams       1         63         22         13     1978
 3     Adams       5         72         23         14     1979
 4     Adams      10         94         36         15     1980
 5     Adams      33        119         42         15     1981
 6     Adams      34        140         49         22     1982

So...save your spreadsheet as a CSV file, get rid of the DATALINES section
in the above job, and substitute your CSV filename in the INFILE
statement...

infile  "<your CSV file>" dsd;

Mike Zdeb
U@Albany School of Public Health
1 University Drive
Rensselaer, NY   12144-3456
(P)518-402-6479
(F)630-604-1475



             "Tonkovich, Mike"
             <Mike.Tonkovich@D
             NR.STATE.OH.US>                                            To
             Sent by: "SAS(r)          SAS-L@LISTSERV.UGA.EDU
             Discussion"                                                cc
             <SAS-L@LISTSERV.U
             GA.EDU>                                               Subject
                                       Code to read a text file

             11/04/2005 12:45
             PM


             Please respond to
             "Tonkovich, Mike"
             <Mike.Tonkovich@D
              NR.STATE.OH.US>






Help Please! I've got the following file sitting in an Excel
spreadsheet.  The file contains 30 fields and 450 records.  Each
"record" contains 5 rows of data.  In the example below, "Adams"

Adams   1977    1978    1979    1980    1981    1982
Fawns           2       1       5       10      33      34
Yearlings       60      63      72      94      119     140
Subadults       24      22      23      36      42      49
Adults          13      13      14      15      15      22

is the county name and "Fawns, Yearlings, Subadults, and Adults" are age
classes.  I would like to create a SAS data set that looks like the
following:

county         fawn     yearling        subadult        adult   year

Adams   2       60              24              13      1977
Adams   1       63              22              13      1978

Etc...

Can anyone provide some suggestions on how I might accomplish this?  I
would really appreciate your help.

Mike

Michael J. Tonkovich, Ph.D.
Wildlife Research Biologist
ODNR, Division of Wildlife
360 E. State St.
Athens, OH  45701
v(740)589.9920 f(740)589.9925
 mike.tonkovich@dnr.state.oh.us