topic Re: Newbie data question in SAS Programming

Newbie data question

deleted_user — Thu, 14 Aug 2008 05:54:36 GMT

Hi All,

I am trying to automate a process of reading from reports that are not really formatted in a great way but am failing miserably at it. Can anyone help me? The data is in a format roughly like this:

REPORT XXX
STATION: ORD
==================
REPORT HEADERS
REPORT HEADERS2
(data starts below)
Origin1 sales2008 sales 2007
Origin2 sales2008 sales 2007
Origin3 sales2008 sales 2007
.
.
.
.
------------------
TTL

This repeats for 40 stations all in the same file. The number of origins can change and is not fixed. What I would like to do is:

1) Read in station name
2) read in top 5 origins
3) read in total (ttl above)
4) Go to the next station.

Any help would be greatly appreciated (and help me learn more about SAS).

Many thanks .

Re: Newbie data question

LinusH — Thu, 14 Aug 2008 14:04:41 GMT

This is handled by some classic data step programming. Could be done i thousands of ways, here's one. This example involves some help variables, retain, if-then-else and scans. If you are going to do more of this kind est you should consider taking a SAS programming class.

data report;
infile '~/reportdat.dat';
input;
length station origin $10
originCount 8
originNext $1;
retain station originCount originNext;
firstVar = scan(_infile_,1,' ');
if firstVar eq 'STATION:' then do;
station = scan(_infile_,2,' ');
originCount = 0;
originNext = 'N';
end;
else if firstVar eq '(data' then originNext = 'Y';
else if firstVar eq 'TTL' or
originNext = 'Y' and originCount lt 5 and firstVar not =: '-' then do;
origin = firstVar;
sales2008 = input(scan(_infile_,2,' '),best.);
sales2007 = input(scan(_infile_,3,' '),best.);
if firstVar eq 'TTL' then originNext = 'N';
else originCount+1;
output;
end;
drop firstVar originCount originNext;
run;

Regards,
Linus

Re: Newbie data question

deleted_user — Thu, 14 Aug 2008 16:06:00 GMT

Thanks so much for the tip Linus. You have opened my eyes. It is sure great learning tricks like this from other users.

All the best.