topic Re: Infile Error in SAS Macro in SAS Programming

Infile Error in SAS Macro

PennyPincher — Mon, 15 Jun 2015 19:20:44 GMT

%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...

Re: Infile Error in SAS Macro

sbb — Mon, 15 Jun 2015 20:19:47 GMT

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.

Re: Infile Error in SAS Macro

ballardw — Mon, 15 Jun 2015 21:17:07 GMT

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.

Re: Infile Error in SAS Macro

Kurt_Bremser — Tue, 16 Jun 2015 05:44:25 GMT

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);

Re: Infile Error in SAS Macro

PennyPincher — Tue, 16 Jun 2015 17:17:23 GMT

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;

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;

Re: Infile Error in SAS Macro

PennyPincher — Tue, 16 Jun 2015 17:18:52 GMT

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;

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;

Re: Infile Error in SAS Macro

PennyPincher — Tue, 16 Jun 2015 17:20:00 GMT

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;

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;

Re: Infile Error in SAS Macro

ballardw — Tue, 16 Jun 2015 21:56:23 GMT

And the line used to invoke the macro looks like?
%GEN_BCListings(bcin=<what did you put here?>);

Re: Infile Error in SAS Macro

PennyPincher — Tue, 16 Jun 2015 22:08:20 GMT

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.

Re: Infile Error in SAS Macro

Kurt_Bremser — Wed, 17 Jun 2015 05:21:00 GMT

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.

Re: Infile Error in SAS Macro

giant_chinese — Wed, 17 Jun 2015 14:21:42 GMT

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.

Re: Infile Error in SAS Macro

Ron_MacroMaven — Wed, 17 Jun 2015 21:37:24 GMT

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.

Re: Infile Error in SAS Macro

data_null__ — Wed, 17 Jun 2015 21:53:22 GMT

*%LET is processed differently in open code vs macro.

*%let bcin='D:/CCIMP/skus-2015-06-08.csv';

%put &=bcin;


%macro test; 
   *%let bcin='D:/CCIMP/skus-2015-06-08.csv';
   %put &=bcin;
   %mend; 
%test();


22         GOPTIONS ACCESSIBLE;
WARNING: Apparent symbolic reference BCIN not resolved.
23         *%let bcin='D:/CCIMP/skus-2015-06-08.csv'; 
24         
25         %put &=bcin;
bcin
26         
27         
28         %macro test;
29            *%let bcin='D:/CCIMP/skus-2015-06-08.csv'; 
30            %put &=bcin;
31            %mend; 
32         %test();
BCIN='D:/CCIMP/skus-2015-06-08.csv'

MPRINT(TEST):   *

Message was edited by: data _null_ Adding MPRINT shows how *%LET is being parsed.

Re: Infile Error in SAS Macro

ballardw — Wed, 17 Jun 2015 22:51:18 GMT

Not to mention that using the * ; form of comment is not recommended. Inside a macro %* ; or /* */ are preferred.

Re: Infile Error in SAS Macro

data_null__ — Thu, 18 Jun 2015 00:50:13 GMT

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.

*%let bcin='D:/CCIMP/skus-2015-06-08.csv';

%put &=bcin;


%macro test; 
   comment%let bcin='D:/CCIMP/skus-2015-06-08.csv'; this is comment text
   %put &=bcin;
   %mend; 

options mprint=1; 
%test();

22         GOPTIONS ACCESSIBLE;
WARNING: Apparent symbolic reference BCIN not resolved.
23         *%let bcin='D:/CCIMP/skus-2015-06-08.csv'; 
24         
25         %put &=bcin;
bcin
26         
27         
28         %macro test;
29            comment%let bcin='D:/CCIMP/skus-2015-06-08.csv'; this is comment text
30            %put &=bcin;
31            %mend; 
32         
33         options mprint=1; 
34         %test();
BCIN='D:/CCIMP/skus-2015-06-08.csv'
MPRINT(TEST):   comment this is comment text

Re: Infile Error in SAS Macro

PGStats — Thu, 18 Jun 2015 01:34:39 GMT

I can see a reason... It is unreadable for us mere mortals

Re: Infile Error in SAS Macro

data_null__ — Thu, 18 Jun 2015 10:58:31 GMT

What is IT?

Re: Infile Error in SAS Macro

Tom — Thu, 18 Jun 2015 11:47:45 GMT

Inside a macro definition use regular comments when you want the comment to appear in the log when MPRINT is on. So when the comment is something that helps the user understand the steps of the program. Use macro comments to comment the macro. So comments that help the coder understand how to maintain the macro, such as why you used a particular option. Use block comments when you need to include semi-colons or unbalanced quotes. Normally in a header block that might include usage examples.

Re: Infile Error in SAS Macro

ballardw — Thu, 18 Jun 2015 14:45:10 GMT

Agreed. But the example was one of the "comment out existing code" which often leads to bad things when it becomes a habit using *; within macros. Especially when & gets involved.

Re: Infile Error in SAS Macro

Tom — Thu, 18 Jun 2015 14:55:47 GMT

That is another discussion.

In the rare cases where I temporarily comment out macro code by adding a * I also make sure to add the * AFTER the % so that it actually becomes a macro comment.