%let bcin='D:/CCIMP/skus-2015-06-08.csv';
data BC_ITEMS (rename= ('Product SKU'n = BC_SKU
'Stock Level'n = BC_QTY)
drop= 'Product UPC/EAN'n) ;
infile &bcin dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat "Product SKU"N $30. ;
informat "Product UPC/EAN"N $1. ;
informat "Stock Level"N best32. ;
format "Product SKU"N $30. ;
format "Product UPC/EAN"N $1. ;
format "Stock Level"N comma12.0 ;
input
"Product SKU"N $
"Product UPC/EAN"N $
"Stock Level"N
;
run;
However, when I surround it with a macro
%macro GEN_BCListings(bcin=);
data BC_ITEMS (rename= ('Product SKU'n = BC_SKU
'
"Product SKU"N $
"Product UPC/EAN"N $
"Stock Level"N
;
etc.....
%mend GEN_BCListings;
I get a string of errors in the log
infile &bcin dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ; informat "Product
______
180
24 ! SKU"N $30. ;
ERROR 180-322: Statement is not valid or it is used out of proper order.
MPRINT(GEN_BCLISTINGS): infile 'D:/CCIMP/skus-2015-06-08.csv' dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
I can't figure it out. Any thoughts are helpful. I guess I'm still rusty...
Personal experience - run again with OPTIONS SOURCE SOURCE2 MGEN SGEN MLOGIC MPRINT;
...and your output looks suspiciously incomplete, mostly because the DATA statement (compiled) does not appear to show/reveal the terminating characters and a semi-colon -- that being the most important info as far as SAS interpreting each source-code statement.
Recommend more self-initiated desk-checking with additional OPTIONS settings for SAS Log reveal.
Scott Barry
SBBWorks, Inc.
Code runs fine until I add the %macro and %mend lines. Then I get an error with the infile statement in the log.
/* Macro 2.2 GEN_BCListings */
%macro GEN_BCListings(bcin=);
LIBNAME CCIMP 'D:\CCIMP\';
*%let bcin='D:/CCIMP/skus-2015-06-08.csv';
data BC_ITEMS (rename= ('Product SKU'n = BC_SKU
'Stock Level'n = BC_QTY)
drop= 'Product UPC/EAN'n);
infile "&bcin" dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat "Product SKU"N $30. ;
informat "Product UPC/EAN"N $1. ;
informat "Stock Level"N best32. ;
format "Product SKU"N $30. ;
format "Product UPC/EAN"N $1. ;
format "Stock Level"N comma12.0 ;
input
"Product SKU"N $
"Product UPC/EAN"N $
"Stock Level"N
;
run;
proc sort data=bc_items out=bc_item_sort;
by bc_sku;
run;
data CCIMP.BCListings (keep= BC_QTY BC_SKU) ;
format po $char10.
subsku 8.;
set bc_item_sort;
p=index(BC_SKU,'.');
po = substr(BC_SKU,1,p-1);
subsku =input(substr(BC_SKU,p+1,length(BC_SKU)-p),8.);
period = find(bc_sku,'.');
if po = ret_po and period NE 0 then do; /*eliminates IC 1344 etc from consideration*/
if subsku LT ret_subsku and ret_period GT 0 then do; /*handles IC 1344 followed by 1344.01*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
if subsku LT ret_subsku and ret_period GT 0 then do; /*second pass for 100*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
if subsku LT ret_subsku and ret_period GT 0 then do; /*second pass for 1000*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
end;
ret_period = period;
ret_po = po;
ret_subsku =subsku;
retain ret_po ret_subsku ret_period;
run;
proc summary data=CCIMP.BCListings ;
format _freq_ comma8.0 qbe_qoht comma8.0 retail_value dollar14.2;
var BC_QTY;
output out=bclisting_stat sum=bc_qty n=bc_sku;
run;
data bclisting_stat2 (keep=runmacro runfile records total_units rundate runtime);
format runmacro $char20. runfile $char60. ;
set bclisting_stat (rename= (_FREQ_ = records bc_qty = Total_Units));
rundate = "&sysdate";
runtime = "&systime";
runfile = &bcin;
runmacro = 'GEN_BCListings';
run;
proc append base=CCIMP.load_stat data=bclisting_stat2;
run;
%mend GEN_BCListings;
Also the entire macro progam and the invocation code would be helpful.
Generally I find defining parameters as:
%let bcin = D:/CCIMP/skus-2015-06-08.csv (without quotes)
and using the quotes at use:
infile file = "&bcin" a tad easier to keep straight.
Keep in mind this runs without issues until I add the %macro and %mend lines
/* Macro 2.2 GEN_BCListings */
%macro GEN_BCListings(bcin=);
LIBNAME CCIMP 'D:\CCIMP\';
*%let bcin='D:/CCIMP/skus-2015-06-08.csv';
data BC_ITEMS (rename= ('Product SKU'n = BC_SKU
'Stock Level'n = BC_QTY)
drop= 'Product UPC/EAN'n);
infile "&bcin" dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat "Product SKU"N $30. ;
informat "Product UPC/EAN"N $1. ;
informat "Stock Level"N best32. ;
format "Product SKU"N $30. ;
format "Product UPC/EAN"N $1. ;
format "Stock Level"N comma12.0 ;
input
"Product SKU"N $
"Product UPC/EAN"N $
"Stock Level"N
;
run;
proc sort data=bc_items out=bc_item_sort;
by bc_sku;
run;
data CCIMP.BCListings (keep= BC_QTY BC_SKU) ;
format po $char10.
subsku 8.;
set bc_item_sort;
p=index(BC_SKU,'.');
po = substr(BC_SKU,1,p-1);
subsku =input(substr(BC_SKU,p+1,length(BC_SKU)-p),8.);
period = find(bc_sku,'.');
if po = ret_po and period NE 0 then do; /*eliminates IC 1344 etc from consideration*/
if subsku LT ret_subsku and ret_period GT 0 then do; /*handles IC 1344 followed by 1344.01*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
if subsku LT ret_subsku and ret_period GT 0 then do; /*second pass for 100*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
if subsku LT ret_subsku and ret_period GT 0 then do; /*second pass for 1000*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
end;
ret_period = period;
ret_po = po;
ret_subsku =subsku;
retain ret_po ret_subsku ret_period;
run;
proc summary data=CCIMP.BCListings ;
format _freq_ comma8.0 qbe_qoht comma8.0 retail_value dollar14.2;
var BC_QTY;
output out=bclisting_stat sum=bc_qty n=bc_sku;
run;
data bclisting_stat2 (keep=runmacro runfile records total_units rundate runtime);
format runmacro $char20. runfile $char60. ;
set bclisting_stat (rename= (_FREQ_ = records bc_qty = Total_Units));
rundate = "&sysdate";
runtime = "&systime";
runfile = &bcin;
runmacro = 'GEN_BCListings';
run;
proc append base=CCIMP.load_stat data=bclisting_stat2;
run;
%mend GEN_BCListings;
And the line used to invoke the macro looks like?
%GEN_BCListings(bcin=<what did you put here?>);
OPTIONS SOURCE SOURCE2 MGEN SGEN MLOGIC MPRINT;
%GEN_BCListings(bcin=D:\CCIMP\skus-2015-06-08.csv);
You can run with or without quotes. It resolved properly.
Before continuing, clean your code and make it more readable.
Since you want valid SAS 7 variable names anyway, use them from the start, and avoid the rename= and the ridiculous 'xxx xxx'n constructs:
%macro GEN_BCListings(bcin);
data BC_ITEMS (drop=product_upc_ean);
infile "&bcin" dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2;
informat
BC_SKU $30.
product_upc_ean $1.
BC_QTY best32.
;
format
BC_SKU $30.
BC_QTY comma12.0
; * no format needed for product_upc_ean, as it is dropped anyway;
input
BC_SKU
product_upc_ean
BC_QTY
;
run;
%mend;
%GEN_BCListings(D:/CCIMP/skus-2015-06-08.csv);
The field names come in that way from external source system. Rather than process twice, the rename statement is used in the first step.
This macro runs fine until I add the %macro and %mend lines. Get an error on the infile statement
/* Macro 2.2 GEN_BCListings */
%macro GEN_BCListings(bcin=);
LIBNAME CCIMP 'D:\CCIMP\';
*%let bcin='D:/CCIMP/skus-2015-06-08.csv';
data BC_ITEMS (rename= ('Product SKU'n = BC_SKU
'Stock Level'n = BC_QTY)
drop= 'Product UPC/EAN'n);
infile "&bcin" dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat "Product SKU"N $30. ;
informat "Product UPC/EAN"N $1. ;
informat "Stock Level"N best32. ;
format "Product SKU"N $30. ;
format "Product UPC/EAN"N $1. ;
format "Stock Level"N comma12.0 ;
input
"Product SKU"N $
"Product UPC/EAN"N $
"Stock Level"N
;
run;
proc sort data=bc_items out=bc_item_sort;
by bc_sku;
run;
data CCIMP.BCListings (keep= BC_QTY BC_SKU) ;
format po $char10.
subsku 8.;
set bc_item_sort;
p=index(BC_SKU,'.');
po = substr(BC_SKU,1,p-1);
subsku =input(substr(BC_SKU,p+1,length(BC_SKU)-p),8.);
period = find(bc_sku,'.');
if po = ret_po and period NE 0 then do; /*eliminates IC 1344 etc from consideration*/
if subsku LT ret_subsku and ret_period GT 0 then do; /*handles IC 1344 followed by 1344.01*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
if subsku LT ret_subsku and ret_period GT 0 then do; /*second pass for 100*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
if subsku LT ret_subsku and ret_period GT 0 then do; /*second pass for 1000*/
BC_SKU =trim(left(bc_sku))||'0';
subsku = subsku *10;
end;
end;
ret_period = period;
ret_po = po;
ret_subsku =subsku;
retain ret_po ret_subsku ret_period;
run;
proc summary data=CCIMP.BCListings ;
format _freq_ comma8.0 qbe_qoht comma8.0 retail_value dollar14.2;
var BC_QTY;
output out=bclisting_stat sum=bc_qty n=bc_sku;
run;
data bclisting_stat2 (keep=runmacro runfile records total_units rundate runtime);
format runmacro $char20. runfile $char60. ;
set bclisting_stat (rename= (_FREQ_ = records bc_qty = Total_Units));
rundate = "&sysdate";
runtime = "&systime";
runfile = &bcin;
runmacro = 'GEN_BCListings';
run;
proc append base=CCIMP.load_stat data=bclisting_stat2;
run;
%mend GEN_BCListings;
The field names do NOT, I repeat, NOT come from the external system. YOUR CODE sets them in the first data step, so they are completely unnecessary, as you immediately change them. Just a waste of screen space and brain cycles.
To clarify why the %macro and %mend statement do not work, provide the log output.
When you added
%macro GEN_BCListings(bcin=);
and put a asterisk before *%let bcin='D:/CCIMP/skus-2015-06-08.csv';
the macrovariable bcin resolves to <empty>.
the infilestatement then resolves to infile dlm = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
it then probably fails on dlm because it tries to resolve dlm as a fieref that doesn't exist.or in syntax after the dlm fileref the '=' doesn't make any sense.
the solution is to provide the value of bcin in the macrocall e.g.
%GEN_BCListings(bcin=D:\CCIMP\skus-2015-06-08.csv);
and the code should run fine.
as soon as you convert the program to a macro
all CR/LF in the input stream, including infile,
are converted to spaces.
Thus the error messages.
Here is the most recent thread in sas communities about this
Here is the Tech Support note
40491 - Use PROC SQL to insert data lines into a macro program
summary: leave it as a program and %include it.
*%LET is processed differently in open code vs macro.
Message was edited by: data _null_ Adding MPRINT shows how *%LET is being parsed.
Not to mention that using the * ; form of comment is not recommended. Inside a macro %* ; or /* */ are preferred.
I don't see any reason to eschew * ; inside a macro. If you want a comment that appears in the log when MPRINT=1 that's what you want. Or course the comment statement works well too.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.