EXPORT AND APPEND TO ONE CSV

[Paul St Louis]

I have a 100 meg file from mainframe application to be converted to one big
clean file. Variables were originally separated by long spaces, which I
replaced with tabs. I then broke the file into several excel sheets, due to
65,000 line limitation, and the need to group certain record together. I
wrote a SAS program to Import all files and sort by a tagset number which
identifies each record. SAS error screen shows window is full. Unsuccessful
to (S)ave or (F) to file. I tried exporting to CSV file, but this just
results in another excel file. How can I export to a DAT file, then keep
appending to it, until I have all the information? I just know somewhere
has been stuck in this situation before. I appreciate any help you care to
share.

Here is what I wrote:

*** PURPOSE: USE SEVERAL IMPORT STATEMENTS TO BUILD
100 MEG DATA TABLE FROM SEPERATE EXCEL FILES. ***;

PROC IMPORT OUT=WORK.VENDMISC
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\MISCITEM.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:B14651";
SHEET=MISC;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=VENDMISC; BY GN;

PROC IMPORT OUT=WORK.SUMMONEA
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM1-2.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:K46357";
SHEET=ONETWOA;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMONEA; BY GN;

PROC IMPORT OUT=WORK.SUMMONEB
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM1-2.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:M5618";
SHEET=ONETWOB;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMONEB; BY GN;

PROC IMPORT OUT=WORK.SUMMTWOA
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM3-4.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:K48756";
SHEET=THRFOURA;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMTWOA; BY GN;

PROC IMPORT OUT=WORK.SUMMTWOB
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM3-4.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:M4996";
SHEET=THRFOURB;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DTA=SUMMTWOB; BY GN;

PROC IMPORT OUT=WORK.SUMMTHRA
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM5-6.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:K47185";
SHEET=FIVSIXA;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMTHRA; BY GN;

PROC IMPORT OUT=WORK.SUMMTHRB
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM5-6.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:M5047";
SHEET=FIVSIXB;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMTHRB; BY GN;

PROC IMPORT OUT=WORK.SUMMSA2
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SA2.mdb.xls"
DBMS=EXCEL REPLACE;
RANGE="A1:M14676";
SHEET=SATAB;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMSA2; BY GN;

PROC IMPORT OUT=WORK.SUMMFORB
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM5-6.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:M5047";
SHEET=FIVSIXB;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMFORB; BY GN;

PROC IMPORT OUT=WORK.CON4
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\CON4.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:E62499";
SHEET=CON4;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=CON4; BY GN;

PROC IMPORT OUT=WORK.CONTREC
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\CONTREC.xls"
DBMS=EXCEL REPLACE;
RANGE="A1:E2951";
SHEET=CONTREC;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=CONTREC; BY GN;

PROC IMPORT OUT=WORK.INV
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\INV.xls"
DBMS=EXCEL REPLACE;
RANGE="A1:F5";
SHEET=INV;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=INV; BY GN;

PROC IMPORT OUT=WORK.SATAB
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SA2.mdb.xls"
DBMS=EXCEL REPLACE;
RANGE="A1:M14676";
SHEET=SATAB;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SATAB; BY GN;

PROC IMPORT OUT=WORK.HDR
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\HDR.xls"
DBMS=EXCEL REPLACE;
RANGE="A1:G4698";
SHEET=HDR;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=HDR; BY GN;

PROC APPEND
BASE=IMP1
DATA=IMP2;
RUN;

PROC IMPORT OUT=WORK.CONSUPP
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\CONSUPP.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:K40766";
SHEET=SUPPLIERS;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=CONSUPP; BY GN;
/*
PROC EXPORT DATA=CONSUPP OUTFILE='T:\Construction Section\Contract Letting
and Processing\Paul\ITEMBUILD\FINALS\COMPILE.TXT' DBMS=TXT REPLACE;
RUN;
*/
PROC PRINT NOOBS;
OPTIONS LS=240 NODATE;
TITLE1;
RUN;

[Howard Schreier <hs AT dc-sug DOT org>]

Post by Paul St Louis
I have a 100 meg file from mainframe application to be converted to one big
clean file. Variables were originally separated by long spaces, which I
replaced with tabs. I then broke the file into several excel sheets, due to
65,000 line limitation, and the need to group certain record together. I
wrote a SAS program to Import all files and sort by a tagset number which
identifies each record.

I think you are making this much too complicated by chopping things up then
putting them back together. What is Excel's value added here? Why not leave
it out of the process?

SAS error screen shows window is full. Unsuccessful

Post by Paul St Louis
to (S)ave or (F) to file.

Is it the Log or the Output window which is full? Which step is running when
it gets full?

What does the log show?

Post by Paul St Louis
I tried exporting to CSV file, but this just
results in another excel file.

Excel can open a CSV file, but that doesn't make the CSV file an Excel file.

Post by Paul St Louis
How can I export to a DAT file,

What the file is named, and how it is structured, are two separate questions.

Post by Paul St Louis
then keep
appending to it, until I have all the information?

I see just one PROC APPEND in the code, and it does not appear to reference
any of the IMPORTed data sets.

You would need an APPEND step for each IMPORT/SORT sequence. But, again, I
would look into bypassing a bunch of this by not breaking up the file in the
first place.

I just know somewhere

Post by Paul St Louis
has been stuck in this situation before. I appreciate any help you care to
share.
*** PURPOSE: USE SEVERAL IMPORT STATEMENTS TO BUILD
100 MEG DATA TABLE FROM SEPERATE EXCEL FILES. ***;
PROC IMPORT OUT=WORK.VENDMISC
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\MISCITEM.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:B14651";
SHEET=MISC;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=VENDMISC; BY GN;
PROC IMPORT OUT=WORK.SUMMONEA
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\SUMM1-2.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:K46357";
SHEET=ONETWOA;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=SUMMONEA; BY GN;

[snip out a bunch more IMPORT/SORT sequences]

Post by Paul St Louis
PROC IMPORT OUT=WORK.HDR
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\HDR.xls"
DBMS=EXCEL REPLACE;
RANGE="A1:G4698";
SHEET=HDR;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=HDR; BY GN;
PROC APPEND
BASE=IMP1
DATA=IMP2;
RUN;
PROC IMPORT OUT=WORK.CONSUPP
DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\CONSUPP.XLS"
DBMS=EXCEL REPLACE;
RANGE="A1:K40766";
SHEET=SUPPLIERS;
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
TEXTSIZE=32767;
RUN;
PROC SORT DATA=CONSUPP; BY GN;
/*
PROC EXPORT DATA=CONSUPP OUTFILE='T:\Construction Section\Contract Letting
and Processing\Paul\ITEMBUILD\FINALS\COMPILE.TXT' DBMS=TXT REPLACE;
RUN;
*/
PROC PRINT NOOBS;
OPTIONS LS=240 NODATE;
TITLE1;
RUN;

[Paul St Louis]

Thanks for your response. Please bear with me...I am feeling sick today (I
suspect a bronchitis revisitation,) and this affects my train of thought
and how well I write. * Answers * below.
3:38 PM >>>

Post by Paul St Louis
I have a 100 meg file from mainframe application to be converted to one big
clean file. Variables were originally separated by long spaces, which I
replaced with tabs. I then broke the file into several excel sheets, due to
65,000 line limitation, and the need to group certain record together. I
wrote a SAS program to Import all files and sort by a tagset number which
identifies each record.

I think you are making this much too complicated by chopping things up then
putting them back together. What is Excel's value added here? Why not leave
it out of the process?

* Answer: *
I suspect you're probably right about my making it too complicated.
However, I just have no clue how to go about cleaning up such a massive
file, retaining unique values, and putting it all back together again. It
was all my little computer could do to load the original Notepad file, and
breaking it into smaller manageable pieces was a very SLOW process, but
allowed me to edit and replace long spaces between values.

Here is a sample value, as it exists in original file:
00000716880000000000000000000008000
Here is how Excel sees this:
7.1688E+29

Importing as a tab-deliminited file keeps the original format, which is
what I need.

Post by Paul St Louis
SAS error screen shows window is full. Unsuccessful to (S)ave or (F) to

file.

Is it the Log or the Output window which is full? Which step is running when
it gets full?

* Answer: * Output Window

What does the log show?

* Answer: *
32 PROC IMPORT OUT=WORK.CONSUPP
33 DATAFILE="T:\Construction Section\Contract Letting and
Processing\Paul\ITEMBUILD\FINALS\CONSUPP.XLS"
34 DBMS=EXCEL REPLACE;
35 RANGE="A1:K40766";
36 SHEET=SUPPLIERS;
37 GETNAMES=YES;
38 MIXED=YES;
39 SCANTEXT=YES;
40 USEDATE=YES;
41 SCANTIME=YES;
42 TEXTSIZE=32767;
43 RUN;
WARNING: SHEET name will be ignored if conflict occurs with RANGE name
specified.
NOTE: WORK.CONSUPP was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 10.98 seconds
cpu time 9.42 seconds
44 PROC SORT DATA=CONSUPP; BY GN;
45
NOTE: There were 40765 observations read from the data set WORK.CONSUPP.
NOTE: The data set WORK.CONSUPP has 40765 observations and 11 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.29 seconds
cpu time 0.29 seconds
46 PROC PRINT NOOBS;
47 OPTIONS LS=240 NODATE;
48 TITLE1;
49 RUN;
NOTE: There were 40765 observations read from the data set WORK.CONSUPP.
NOTE: PROCEDURE PRINT used (Total process time):
real time 36.31 seconds
cpu time 1.65 seconds

* Answer Cont. *
I see now that I should have typed the name of the library to save the
contents. I tried that this time, and it worked. Not sure how to move
contents to a file, but it worked. Yes, it's fun being a newbie...

Post by Paul St Louis
I tried exporting to CSV file, but this just results in another excel file.

Excel can open a CSV file, but that doesn't make the CSV file an Excel file.

* Answer *
I didn't know that. The file had an Excel icon and loaded in Excel. I just
now loaded in Word and it worked fine. Who knew? Obivously not me.

Post by Paul St Louis
How can I export to a DAT file,

What the file is named, and how it is structured, are two separate
questions.

* Answer *
True. Wouldn't bother me a bit if each value was separated with fruit
loops, just as long as I could get a usuable file in the end.

Post by Paul St Louis
then keep appending to it, until I have all the information?

I see just one PROC APPEND in the code, and it does not appear to reference
any of the IMPORTed data sets.

* Answer *
I pasted that just as a placeholder and was not done troubleshooting yet. I
couldn't see finishing the code to clump all of the imports together if SAS
was stating Output window too full for just one of them.

You would need an APPEND step for each IMPORT/SORT sequence. But, again, I
would look into bypassing a bunch of this by not breaking up the file in the
first place.

* Answer *
Would very much like to do that. However, file size prevented it. Either
our computers are too wimpy, or it's just more than the server can handle.

I welcome any additional thoughts. Take care.