BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kiddcao
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
mohamed_zaki
Barite | Level 11


Part 2:

  1. *Combine CASE File/
  2. proc  datasets library=Raw nolist;
  3. %macro Combine_data(ProviderID=);
  4. append base=Raw._0 data=Raw._&ProviderID force;
  5. run;
  6. %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

View solution in original post

7 REPLIES 7
mohamed_zaki
Barite | Level 11


Part 2:

  1. *Combine CASE File/
  2. proc  datasets library=Raw nolist;
  3. %macro Combine_data(ProviderID=);
  4. append base=Raw._0 data=Raw._&ProviderID force;
  5. run;
  6. %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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

Ksharp
Super User

data dir;

     length buffer $200.;

     infile tmp dsd lrecl=32767 missover length=len ;

     input buffer $varying200.  len ;

run;

Rick_SAS
SAS Super FREQ

> 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
Calcite | Level 5

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
Super User

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 7 replies
  • 1784 views
  • 7 likes
  • 5 in conversation