SAS Macro Combining Data Statement is not valid or it is out of proper order

[kiddcao]

Hey folks,

I just started to learn macro and want to accomplish a few things with my current codes.

1. dynamic import many csv files into sas (which should be done)

2. combining the imported data file and make it into one giant file.

The first part works well however the second part (bold) does not work and has the log errors. I basically used the same code but don't why there is the Statement is not valid or it is out of proper order

Any comment will be greatly appreciated!!

Part1:

*CASE base Upload/
%macro import_data(ProviderID=);
proc import datafile="/PROJECTS/BMW/3D/RAW/CASE/BASE/&ProviderID._CASE_Base.csv" dbms=csv out=Raw._&ProviderID  replace;
run;

%mend import_data;

data data_torun;
input filenum;
datalines;
0
1389
2690
3163
3168
3169
;;;;
run;

proc sql;
select cats('%import_data(ProviderID=',filenum,')') into :listtorun
separated by ' '
from data_torun;
quit;

&listtorun.;

Part 2:

*Combine CASE File/
proc  datasets library=Raw nolist;

%macro Combine_data(ProviderID=);
append base=Raw._0 data=Raw._&ProviderID force;
run;
%mend Combine_data;

data data_torun1;
input filenum;
datalines;
1389
2690
3163
3168
3169
;;;;
run;

proc sql;
select cats('%Combine_data(ProviderID=',filenum,')') into :listtorun1
separated by ' '
from data_torun1;
quit;

&listtorun1.;

Log

NOTE:
Compression was disabled for data set WORK.DATA_TORUN1 because compression
overhead      would increase the size of the data set.

NOTE:
The data set WORK.DATA_TORUN1 has 5 observations and 1 variables.

NOTE:
DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

4046  &listtorun1.;

NOTE:
Line generated by the invoked macro "COMBINE_DATA".

4046    append base=Raw._0
data=Raw._&ProviderID force;

       ------

        180

MPRINT(COMBINE_DATA):   append base=Raw._0 data=Raw._1389 force;

ERROR
180-322: Statement is not valid or it is used out of proper order.

MPRINT(COMBINE_DATA):   run;

NOTE:
Line generated by the invoked macro "COMBINE_DATA".

4046   append base=Raw._0 data=Raw._&ProviderID
force;

       ------

       180

MPRINT(COMBINE_DATA):   append base=Raw._0 data=Raw._2690 force;

ERROR
180-322: Statement is not valid or it is used out of proper order.

MPRINT(COMBINE_DATA):   run;

NOTE:
Line generated by the invoked macro "COMBINE_DATA".

4046   append base=Raw._0 data=Raw._&ProviderID
force;

       ------

       180

[mohamed_zaki · Accepted solution]

Part 2:

*Combine CASE File/
proc  datasets library=Raw nolist;
%macro Combine_data(ProviderID=);
append base=Raw._0 data=Raw._&ProviderID force;
run;
%mend Combine_data;

Do you want to use the APPEND statement of PROC DATASET, so why the line 2 before  line 3

Or do you want to use PROC APPEND?

check this PROC APPEND Alternatives

[RW9]

Hi,

This has been covered many times on this forum.  You don't need macros for this.  Also it is not good form to put macro statements in actual code like that.

Simple code is:

/* Assuming you want a list of files from one directory */

filename tmp pipe 'dir "c:\temp\*.xls" /b';

data dir;

     length buffer $200.;

     infile tmp dsd lrecl=32767 missover;

     input buffer $varying200.;

run;

/* Now for each filename in the list import and add to big dataset - note that each import file needs to be very similar or you will get warnings/errors!! */

data _null_;

     set dir;

     if _n_=1 then call execute('proc import datafile="'||strip(buffer)||'" out=work.total replace; run;');

     else call execute('proc import datafile="'||strip(buffer)||'" out=tmp replace; run;

                                  data total; set total tmp; run;');

run;

[kiddcao]

Hi RW9, when I run your code it has the "Variable name containing length expected after $VARYING200", how can I solve it? thanks!

[Ksharp]

data dir;

     length buffer $200.;

     infile tmp dsd lrecl=32767 missover length=len ;

     input buffer $varying200.  len ;

run;

[Rick_SAS]

> Any comment will be greatly appreciated!!

There is a special Support Community for questions about macro and the DATA step: https://communities.sas.com/community/support-communities/sas_macro_facility_data_step_and_sas_langu...

[kiddcao]

Hey guys thank so much! Yep it is just a silly mistake that I forget to add PROC before amend...it works well now. Again it is my second week of experience in SAS so I am a bit lost. Thanks again1

[Ksharp]

It seems that it is nothing with IML . You misunderstand what macro should do .

%macro Combine_data(ProviderID=);

append base=Raw._0 data=Raw._&ProviderID force;

run;

%mend Combine_data;

*Combine CASE File/
proc  datasets library=Raw nolist;

%Combine_data(ProviderID=1)

%Combine_data(ProviderID=2)

...................

Xia Keshan