DATA Step, Macro, Functions and more

Infile Error in SAS Macro

Reply
Occasional Contributor
Posts: 5

Infile Error in SAS Macro

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

Super Contributor
Super Contributor
Posts: 3,174

Re: Infile Error in SAS Macro

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.

Occasional Contributor
Posts: 5

Re: Infile Error in SAS Macro

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;

Super User
Posts: 10,500

Re: Infile Error in SAS Macro

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.

Occasional Contributor
Posts: 5

Re: Infile Error in SAS Macro

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;

Super User
Posts: 10,500

Re: Infile Error in SAS Macro

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

Occasional Contributor
Posts: 5

Re: Infile Error in SAS Macro

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.

Super User
Posts: 6,936

Re: Infile Error in SAS Macro

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Infile Error in SAS Macro

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;

Super User
Posts: 6,936

Re: Infile Error in SAS Macro

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New User
Posts: 1

Re: Infile Error in SAS Macro

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.

Regular Contributor
Posts: 198

Re: Infile Error in SAS Macro

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.

Respected Advisor
Posts: 3,777

Re: Infile Error in SAS Macro

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

Super User
Posts: 10,500

Re: Infile Error in SAS Macro

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

Respected Advisor
Posts: 3,777

Re: Infile Error in SAS Macro

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


Ask a Question
Discussion stats
  • 19 replies
  • 556 views
  • 0 likes
  • 9 in conversation