BookmarkSubscribeRSS Feed
PennyPincher
Calcite | Level 5

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

19 REPLIES 19
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

PennyPincher
Calcite | Level 5

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;

ballardw
Super User

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.

PennyPincher
Calcite | Level 5

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;

ballardw
Super User

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

PennyPincher
Calcite | Level 5

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.

Kurt_Bremser
Super User

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

PennyPincher
Calcite | Level 5

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;

Kurt_Bremser
Super User

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.

giant_chinese
Calcite | Level 5

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.

Ron_MacroMaven
Lapis Lazuli | Level 10

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.

data_null__
Jade | Level 19

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

ballardw
Super User

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

data_null__
Jade | Level 19

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1692 views
  • 0 likes
  • 9 in conversation