I have to run a process where I import a number of CSV files into SAS and then append them all together. I have to do this with a number of different types of files so I'm trying to create some code that is generic and will work for each type.
I've managed to write a macro to identify all of the different files of a given type to be uploaded and import them into SAS. The issue I'm having is that whilst all the files of a given type have the same fields, due to the fact I am importing a CSV file, the lengths of the individual fields will vary between the files.
What I'm trying to do is write some code that establishes the longest length of the various fields, creates a dataset with those lengths, and then appends the files to that dataset.
I've got all of the datasets stored in macro variables named "SASNameMX" where the X is a number that goes from 1 upwards. I also have a macro variable "Counter" which stores what the maximum value of X is.
Given all this, where I've got to is below (all in a macro).
************************* SAS CODE ***************************
* CREATE THE "IN" LIST FOR ESTABLISHING THE LENGTHS OF THE FIELDS;
%Do j = 1 %To &Counter;
%If &j = 1 %Then %Do; %Let Inclusions = %Str(%()%Str(%")&&SASNameM&j%Str(%"); %End;
%Else %Do; %Let Inclusions = &Inclusions,%Str(%")&&SASNameM&j%Str(%"); %End;
%End;
%Let Inclusions = &Inclusions%Str(%));
* ESTABLISH THE MAXIMUM LENGTH OF THE CHARACTER FIELDS AND THE OTHER VARIABLES THAT ARE IN THE LIST AND STORE IT IN A VARIABLE "LENGTHS";
Proc SQL NoPrint;
Select CatX(" ",Name,Type,Length) Into : Lengths Separated By " " From
Select Name
, Case
When UpCase(Type) = 'CHAR' Then '$'
Else ' '
End As Type
, Max(Length) As Length
From Dictionary.Columns
Where UpCase(Libname) = UpCase('&Lib1')
%If &Inclusions NE %Then %Do;
And UpCase(MemName) In &Inclusions
%End;
Group By 1,2); Quit;
*ESTABLISH THE FORMAT FOR THE DATE / TIME VARIABLES;
Proc Sql NoPrint;
Select CatX(" ",Name,Format) Into : SetFormats Separated By ' ' From
(Select Distinct
Name
, Format
From Dictionary.Columns
Where UpCase(Libname) = UpCase('&Lib1')
And UpCase(Format) In ('TIME.','DATETIME.','DDMMYY10.','MMDDYY10.','DATE.')
%If &Inclusions NE %Then %Do;
And UpCase(MemName) In &Inclusions
%End;
);
*CREATE AN EMPTY DATA SET WITH THE FIELDS DEFINED;
Data WORK.FINAL;
Length &Lengths;
Format &SetFormats;
Stop;
Run ;
*APPEND ALL OF THE FILES TO THE EMPTY DATASET;
%Do i = 1 %To &Counter;
Proc Append Base=WORK.FINAL Data=WORK..&&SASNameM&i Force;
Run; Quit;
%End;
************* END SAS CODE *******************
The issue I'm getting is with the PROC SQL statements as they are falling over and not providing me with a reason. When I copy them from the log and put them in the programme editor they work properly, so I am completely dumbfounded.
The log that I get is at the end of this post. From the log, it looks like the error is perhaps before the SQL statement, but I'm at a loss for where it is. Any one able to point out the obvious mistake?
Many thanks
Ian
**************** SAS LOG *******************************
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable J resolves to 12
SYMBOLGEN: Macro variable SASNAMEM12 resolves to AU_OPP_20100403_20100507
SYMBOLGEN: Macro variable J resolves to 13
SYMBOLGEN: Macro variable INCLUSIONS resolves to
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","A
U_OPP_20090328_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_O
PP_20090627_20090731","AU_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_
20090926_20091231","AU_OPP_20100101_20100402","AU_OPP_20100403_20100507"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable J resolves to 13
SYMBOLGEN: Macro variable SASNAMEM13 resolves to AU_OPP_20100508_20100604
SYMBOLGEN: Macro variable INCLUSIONS resolves to
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","A
U_OPP_20090328_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_O
PP_20090627_20090731","AU_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_
20090926_20091231","AU_OPP_20100101_20100402","AU_OPP_20100403_20100507","AU_OPP_201
00508_20100604"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable COUNTER resolves to 13
SYMBOLGEN: Macro variable APPENDER resolves to YES
MPRINT(IMPLOOP): Proc SQL NoPrint;
SYMBOLGEN: Macro variable LIB1 resolves to Work
SYMBOLGEN: Macro variable INCLUSIONS resolves to
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","A
U_OPP_20090328_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_O
PP_20090627_20090731","AU_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_
20090926_20091231","AU_OPP_20100101_20100402","AU_OPP_20100403_20100507","AU_OPP_201
00508_20100604")
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable INCLUSIONS resolves to
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","A
U_OPP_20090328_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_O
PP_20090627_20090731","AU_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_
20090926_20091231","AU_OPP_20100101_20100402","AU_OPP_20100403_20100507","AU_OPP_201
00508_20100604")
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
-
22
76
MPRINT(IMPLOOP): Select CatX(" ",Name,Type,Length) Into : Lengths Separated By " " From
(Select Name , "$" As Type , Max(Length) As Length From Dictionary.Columns Where
UpCase(Libname) = UpCase("Work") And UpCase(Type) = "CHAR" And UpCase(MemName) In
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","AU_OPP_200903
28_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_OPP_20090627_20090731","A
U_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_20090926_20091231","AU_OPP_20100101_
20100402","AU_OPP_20100403_20100507","AU_OPP_20100508_20100604") Group By 1);
ERROR 22-322: Syntax error, expecting one of the following: a quoted string,
a numeric constant, a datetime constant, a missing value, (, -, SELECT.
ERROR 76-322: Syntax error, statement will be ignored.
MPRINT(IMPLOOP): Quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MPRINT(IMPLOOP): Proc Sql NoPrint;
SYMBOLGEN: Macro variable INCLUSIONS resolves to
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","A
U_OPP_20090328_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_O
PP_20090627_20090731","AU_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_
20090926_20091231","AU_OPP_20100101_20100402","AU_OPP_20100403_20100507","AU_OPP_201
00508_20100604")
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
SYMBOLGEN: Macro variable INCLUSIONS resolves to
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","A
U_OPP_20090328_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_O
PP_20090627_20090731","AU_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_
20090926_20091231","AU_OPP_20100101_20100402","AU_OPP_20100403_20100507","AU_OPP_201
00508_20100604")
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
-
22
76
MPRINT(IMPLOOP): Select CatX(" ",Name,Format) Into : SetFormats Separated By ' ' From (Select
Distinct Name , Format From Dictionary.Columns Where UpCase(Libname) = UpCase('&Lib1') And
UpCase(Format) In ('TIME.','DATETIME.','DDMMYY10.','MMDDYY10.','DATE.') And UpCase(MemName) In
("AU_OPP_20090101_20090130","AU_OPP_20090131_20090227","AU_OPP_20090228_20090327","AU_OPP_200903
28_20090501","AU_OPP_20090502_20090529","AU_OPP_20090530_20090626","AU_OPP_20090627_20090731","A
U_OPP_20090801_20090828","AU_OPP_20090829_20090925","AU_OPP_20090926_20091231","AU_OPP_20100101_
20100402","AU_OPP_20100403_20100507","AU_OPP_20100508_20100604") );
ERROR 22-322: Syntax error, expecting one of the following: a quoted string,
a numeric constant, a datetime constant, a missing value, (, -, SELECT.
ERROR 76-322: Syntax error, statement will be ignored.
MPRINT(IMPLOOP): Quit;