topic Having problem reading in a file. in SAS Procedures

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 00:05:13 GMT

For the most part I have been reading in data horizontally but I now have a data set that has data written vertically. I am having trouble reading this data correctly. This data file has 8000+ observations but I will simply list out the first few so you know what I'm talking about.

In the file, there are names, addresses, and telephone numbers of most of the child care centers in North Carolina (total ~ 8000). Each center takes six records in the file. The first record is the eight digit license number; the second record is the name; the third is the street address; the fourth record is the town, state (NC), and zip code in columns 31-35. In the fifth record is the telephone number in the form (xxx) xxx-xxxx where the first field is the area code. The sixth record gives what class of center (e.g. 'Five Star') and what type of license. On this last record, we only need to read in the class, which is the first field in that record. Most of the values of class are 'One' to 'Five' while other values include 'Temporary' and a statute designation. I need to write a SAS program to read in this information, where each center corresponds to an observation with (at least) variables for the license number, zip code, area code, and class of center (character variable).

I have tried a good amount of things but still have not been able to get this to work correctly. I am mostly dealing with simply

data child;

infile 'file location' ;

input ______;

run;

I am allowed to use @@ or a do loop and also keep, drop commands. Some other things that may be mentioned here on the forums, I may not have learned yet.

EDIT: I have now attached the file to my question. Also, if you're interested in helping me well this is actually course work for a class found at http://www.stat.ncsu.edu/people/monahan/courses/st445/

if you want to see the things we've done so I don't step out my bounds. I know that I am supposed to "Figure it out by myself" but for these kind of things I learn best by example. I did my previous homework for this class by using the examples he showed us in class. However, for this assignment (Homework 3) I have spent a few hours on it but still cannot figure it out. I would appreciate it if someone could at least get me started, I understand that inputting the file is the hardest part. I can probably do the other parts by myself just fine.

Having problem reading in a file.

Patrick — Sun, 26 Feb 2012 00:39:09 GMT

Below link should answer your question:

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a002001051.htm

Having problem reading in a file.

art297 — Sun, 26 Feb 2012 00:46:47 GMT

If you really have exactly 6 records per file, and each captures the same fields, you could use something like:

data have;

informat stuff $80.;

input stuff &;

cards;

68000482

CHILDREN'S CAMPUS OF CHAPEL HILL II

1620 MLK JR BLVD

CHAPEL HILL, NC 27514

(919) 967-5020

Temporary License

92002687

"HAPPY FACES" CHILD CARE CENTER

4500 EMMIT DRIVE

RALEIGH, NC 27604

(919) 231-0783

One Star Family CC Home License

60002518

'A' IS FOR APPLE

11919 PLANTERS ESTATES DRIVE

CHARLOTTE, NC 28278

(704) 504-8977

Five Star Center License

32001484

'A' PRECIOUS ANGEL DAYCARE

916 SOUTH MINERAL SPRINGS ROAD

DURHAM, NC 27703

(919) 957-8496

Three Star Family CC Home License

64000406

'AGAPE' CENTER OF LOVE'

203 N WALNUT STREET

SPRING HOPE, NC 27882

(252) 885-6249

GS 110-106

;

data want (drop=stuff);

set have;

retain id org add: phone;

if mod(_n_,6) eq 1 then id=strip(stuff);

else if mod(_n_,6) eq 2 then org=strip(stuff);

else if mod(_n_,6) eq 3 then address1=strip(stuff);

else if mod(_n_,6) eq 4 then address2=strip(stuff);

else if mod(_n_,6) eq 5 then phone=strip(stuff);

else do;

license=stuff;

output;

end;

run;

Re: Having problem reading in a file.

Tom — Sun, 26 Feb 2012 01:19:31 GMT

Start simple. Read the 6 lines into 6 character variables so that you can make sure the format is consistent.

data want ;

infile 'myfile' truncover ;

input #1 line1 $200.

#2 line2 $200.

#3 line3 $200.

#4 line4 $200.

#5 line5 $200.

#6 line6 $200.

;

run;

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 01:23:11 GMT

Well it's not exactly like that, It's one big file with 8000+ records. I just gave an example of what the file looks like. It's data in that format all the way down with a total of ~8000 "observations"(this is with all the 6 variables). I also have to somehow split the zip from the city and state, and split the area code from the phone number. I have to use an infile statement instead of cards because of such a huge data file. I don't believe I've learned what "informat" does and also what the & does in the input statement. I've done mostly either character variables(denoted by $) or numeric(no format or the default). But the code at the bottom does seem helpful and I think I might have to do something along those lines.

Re: Having problem reading in a file.

art297 — Sun, 26 Feb 2012 02:18:21 GMT

If your entire file is as clean as your example data, then you might be able to get away with code as simple as:

data want;

informat id $10.;

informat org address1 citystate $60.;

informat areacode $3.;

informat phone $10.;

informat license $50.;

infile "c:\art\havedata.txt";

input id

/org &

/address1 &

/citystate & zipcode

/ @2 areacode @7 phone

/license;

run;

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 03:31:47 GMT

I just tried doing multiple input statements but my log still gives me errors. I tried this just to see what would happen.

data child;

infile 'E:\School Stuff\ST445\child4.txt' obs=2 pad;

input centerid;

input name $ 1-36;

input address $ 1-36;

input townstatezip $ 1-36 ;

input phone $ 1-36 ;

input class $ 1-36;

put _all_;

run;

Also the log will tell me that I have a "Lost card" what does this mean exactly? Log: "

NOTE: LOST CARD.

centerid=68000482 name=CHILDREN'S CAMPUS OF CHAPEL HILL II address= townstatezip= phone= class=

_ERROR_=1 _N_=1

NOTE: 2 records were read from the infile 'E:\School Stuff\ST445\child4.txt'.

The minimum record length was 8.

The maximum record length was 35."

Having problem reading in a file.

PGStats — Sun, 26 Feb 2012 03:42:27 GMT

Start with Tom's suggestion below and build from there. Its the wise thing to do.

Re: Having problem reading in a file.

art297 — Sun, 26 Feb 2012 03:44:13 GMT

I disagree. If his data are as clean as his example, the code I suggested witl do everything he needs in one pass:

data want;

informat id $10.;

informat org address1 citystate $60.;

informat areacode $3.;

informat phone $10.;

informat license $50.;

infile "c:\art\havedata.txt";

input id

/org &

/address1 &

/citystate & zipcode

/ @2 areacode @7 phone

/license;

run;

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 03:49:45 GMT

We haven't done anything with "informat" yet so I don't think we can use that, also we can't use formatting with the "/" command. By the way I tested your code to see if it would work but it still doesn't. I can attach the actual file to my original post if that would help.

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 04:00:38 GMT

I just added/edited some details to my original post, I don't know if doing so will let others know that I did so, so I'm making a post about it.

Having problem reading in a file.

art297 — Sun, 26 Feb 2012 04:15:08 GMT

Using #1 etc., like Tom suggested, is the same thing as using / to change records. Formally defining informats with informat statements is the same thing as putting them on the input line.

Try the following. I believe it covers all of your exceptions:

data want;

infile "c:\art\child4.txt" truncover;

input #1 id $10.

#2 org & : $60.

#3 address1 & : $60.

#4 citystate & : $60. zipcode $10.

#5 @2 areacode $3. @7 phone $8.

#6 license & : $50.;

run;

Having problem reading in a file.

Patrick — Sun, 26 Feb 2012 04:30:40 GMT

The code you've previously posted would have worked.

The issue you had there was "obs=2" in the infile statement but at the same time 6 input statements (=trying to read 6 lines in one iteration of the datastep). That's where the "lostcard" came from.

Below code will run. I prefer "truncover" over "pad" - but both is o.k. in your case.

data want;
/* infile 'C:\temp\child4.txt' truncover lrecl=100;*/
infile 'C:\temp\child4.txt' /*obs=6*/ pad;
input centerid 15.;
input name $ 1-36;
input address $ 1-36;
input townstatezip $ 1-36 ;
input phone $ 1-36 ;
input class $ 1-36;
run;

proc print data=want;
run;

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 04:39:10 GMT

Well this "works" but it uses things we haven't learned so I'm pretty sure the teacher would be suspicious about what I did lol...

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 04:41:32 GMT

Thanks, now I just have to figure out how to get the 4 he really wants, I know I need to use "drop" for some but I still have to somehow seperate the zip code from the city, state and the area code from the full phone number.

Having problem reading in a file.

art297 — Sun, 26 Feb 2012 04:43:55 GMT

Look at your data. Every field is in certain columns. I'll leave the rest for you to figure out.

Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 19:53:07 GMT

What is the syntax that I am supposed to use if I want to "call" the columns that I need. I know I would have to probably make a zip and areacode variable but how can I make it so that it's like zip = townstatezip's column 31-36?

Re: Having problem reading in a file.

FriedEgg — Sun, 26 Feb 2012 21:24:56 GMT

Pretty much the same as others provided:

data foo;

infile cards truncover dlm=', ()';

input #1 id

#2 company_name $36.

#3 street $36.

#4 city & :$26. state :$10. zip :$5.

#5 arecode :$3. phone :$36.

#6 policy $36.;

cards;

68000482

CHILDREN'S CAMPUS OF CHAPEL HILL II

1620 MLK JR BLVD

CHAPEL HILL, NC 27514

(919) 967-5020

Temporary License

92002687

"HAPPY FACES" CHILD CARE CENTER

4500 EMMIT DRIVE

RALEIGH, NC 27604

(919) 231-0783

One Star Family CC Home License

60002518

'A' IS FOR APPLE

11919 PLANTERS ESTATES DRIVE

CHARLOTTE, NC 28278

(704) 504-8977

Five Star Center License

32001484

'A' PRECIOUS ANGEL DAYCARE

916 SOUTH MINERAL SPRINGS ROAD

DURHAM, NC 27703

(919) 957-8496

Three Star Family CC Home License

64000406

'AGAPE' CENTER OF LOVE'

203 N WALNUT STREET

SPRING HOPE, NC 27882

(252) 885-6249

GS 110-106

;

run;

Obs	id	company_name	street	city	state	zip	arecode	phone	policy
1	68000482	CHILDREN'S CAMPUS OF CHAPEL HILL II	1620 MLK JR BLVD	CHAPEL HILL	NC	27514	919	967-5020	Temporary License
2	92002687	"HAPPY FACES" CHILD CARE CENTER	4500 EMMIT DRIVE	RALEIGH	NC	27604	919	231-0783	One Star Family CC Home License
3	60002518	'A' IS FOR APPLE	11919 PLANTERS ESTATES DRIVE	CHARLOTTE	NC	28278	704	504-8977	Five Star Center License
4	32001484	'A' PRECIOUS ANGEL DAYCARE	916 SOUTH MINERAL SPRINGS ROAD	DURHAM	NC	27703	919	957-8496	Three Star Family CC Home License
5	64000406	'AGAPE' CENTER OF LOVE'	203 N WALNUT STREET	SPRING HOPE	NC	27882	252	885-6249	GS 110-106

Message was edited by: Matthew Kastin I noticed you wanted to separate area code and phone number too.

Having problem reading in a file.

Tom — Sun, 26 Feb 2012 21:42:21 GMT

That is called column mode input. Here is the link to the Help page for it.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000148066.htm

You should read the other pages on the other ways that the INPUT statement can be used.

Re: Having problem reading in a file.

Leon27607 — Sun, 26 Feb 2012 22:01:31 GMT

Not exactly what I was asking. I was asking is it possible to only keep my original 6 variables but somehow create a new variable(without an input statement) that would only use parts of the previously created variable?

I have this so far but there's parts of it that needs to be changed because it doesn't work.

data child;

infile 'E:\School Stuff\ST445\child4.txt' pad; *Reading the file in and padding the end with spaces so it can tell when a new record begins*/;

input centerid; *Inputting every variable in one by one because they are given vertically*/;

input name $ 1-36;

input address $ 1-36;

input townstatezip $ 1-36 ;

input aphone $ 1-36 ;

input class $ 1-36;

stars = .;

if (class = 'One') then stars = 1;

else if (class = 'Two') then stars = 2;

else if (class = 'Three') then stars = 3;

else if (class = 'Four') then stars = 4;

else if (class = 'Five') then stars = 5;

if (areacode != 828 or 704 or 980 or 336 or 919 or 910 or 252) then put 'Error';

run;

In my assignment I have to change the "Class" to a variable and one, two, three, four, five obviously have different lengths for different records. I understand that if I read that line as 2 variables I could just use free format but like I said I was wondering if I could create a new variable without having to use "input."