topic How to import multiple excel files using Macros's language in SAS Programming

How to import multiple excel files using Macros's language

Marwa_Se — Tue, 04 Jul 2017 12:49:05 GMT

Hello, i decided to ask for help after multiple trials ...

every month i have to import 3 excel files of the last 3 months (for example: for april may and june: fichier_04_2017, fichier_05_2017, fichier_ 06_2017) with the intention of doing a comparison:

How could i automate the macros variable in order to import files without having to change the month or the name

i calculated months and years like mentionned below

Data annee_mois;

Attrib maintenant a b c format = date9.;

maintenant = today();

a = (intnx('month',maintenant,-1));

b = (intnx('month',maintenant,-2));

c = (intnx('month',maintenant,-3));

a1 = put(year(a),$4.);

a2 = put(year(b),$4.);

a3 = put(year(c),$4.);

M1 = put(month(a),$2.);

M2 = put(month(b),$2.);

M3 = put(month(c),$2.);

Call symput('a1',a1);

Call symput('M1',M1);

Call symput('a2',a2);

Call symput('M2',M2);

Call symput('a3',a3);

Call symput('M3',M3);

Run;

And then i created my macro :

%macro importer (infile,outfile);

Proc import Out = swork.&infile (where =( Origine_score in ('Client scoré') and type_personne in ('Client') ) )

Datafile = "adress\&outfile..xlsx"

Dbms = xlsx replace;

Run;

%mend importer;

%importer(fichier_&m1._&a1.,outils_rouges_&m1._&a1.);

i got this error message:

NOTE 138-205: Line generated by the macro variable "INFILE".

1 swork.outils_rouges_&m1._&a1.

ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS,

DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.

NOTE: Line generated by the macro variable "INFILE".

1 swork.outils_rouges_&m1._&a1.

ERROR 76-322: Syntax error, statement will be ignored.

WARNING: Apparent symbolic reference M1 not resolved.

WARNING: Apparent symbolic reference A1 not resolved.

WARNING: Apparent symbolic reference M1 not resolved.

WARNING: Apparent symbolic reference A1 not resolved.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 secondes

cpu time 0.00 secondes

NOTE: The SAS System stopped processing this step because of errors.

Could someone help me to find out this problem, i think it didnt recognized the macro variable (m1, a1) which were declared at the beginning,

Thank you a lot for your help,

Marwa 🙂

Re: How to import multiple excel files using Macros's language

RW9 — Tue, 04 Jul 2017 13:14:34 GMT

The reason is those macro variables are not Global but local outside the macro - you can check the documentation on variable scope.

I would do:

data _null_;
  do i=0 to 2;
    import_date=put(intnx("month",today()-2,i),yymm7.);
    call execute(cats('proc import datafile="fichier_',import_date,'.xlsx out=import',put(i,1.),' replace; run;'));
  end;
run;

This does the 3 months of -3 to today() and creates the proc import step for each one.

Re: How to import multiple excel files using Macros's language

Kurt_Bremser — Tue, 04 Jul 2017 13:18:14 GMT

First of all, take a look at your log and get rid of the WARNING messages caused by using a character format on a numeric variable:

data annee_mois;
attrib maintenant a b c format = date9.;
maintenant = today();
a = (intnx('month',maintenant,-1));
b = (intnx('month',maintenant,-2));
c = (intnx('month',maintenant,-3));
a1 = put(year(a),z4.);
a2 = put(year(b),z4.);
a3 = put(year(c),z4.);
m1 = put(month(a),z2.);
m2 = put(month(b),z2.);
m3 = put(month(c),z2.);
call symput('a1',a1);
call symput('m1',m1);
call symput('a2',a2);
call symput('m2',m2);
call symput('a3',a3);
call symput('m3',m3);
run;

Note the use of the Zw. format to preserve leading zeroes and prevent blanks.

Now, if you run this immediately afterwards:

%macro importer (infile,outfile);
/*
Proc import Out = swork.&infile (where =( Origine_score in ('Client scoré') and type_personne in ('Client') ) )
Datafile = "adress\&outfile..xlsx"
Dbms = xlsx replace;
Run;
*/
%put &infile &outfile;
%mend importer;

%importer(fichier_&m1._&a1.,outils_rouges_&m1._&a1.);

you will find two valid dataset/file names in the log, so the import step should work.

Re: How to import multiple excel files using Macros's language

Marwa_Se — Tue, 04 Jul 2017 13:56:12 GMT

Thank you for your quickly answer 🙂 i'm not familiar with the macro language that's why i can't differentiate between local and global variables,

i tried your code, it seems answering my question but i got an error message again

i think there is something wrong with the call execute. In fact, should i mention quotation marks between the whole path after the datafile ?

data _null_;

do i=0 to 2;

import_date=put(intnx("month",today()-2,i),yymm7.);

call execute(cats('proc import datafile="C:\Users\VFMarwa\Desktop\Perso\Marwa Perso\Marwa\outils_rouges_',

import_date,'.xlsx out=import',put(i,1.),' replace; run;'));

end;

run;

Anyway i got this error message

NOTE: Line generated by the CALL EXECUTE routine.

3 + proc import

----

202

3 !+datafile="\\VFAPP007\appli\ADMBANQ\ADM_INFORMATIONNEL\Conformité\LAB_vigilance\outils_roug

3 !+es_2017M09.xlsx out=import2replace; run;"

ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS,

DEBUG, FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

I'm really a beginner with macro my question may seems evident for you ...

Re: How to import multiple excel files using Macros's language

Marwa_Se — Tue, 04 Jul 2017 13:59:16 GMT

@Kurt_Bremser thank you for your answer, but i think that my problem is related to global and local variables ...

i will try to work with the call excute ...

i still have errors with the code above

Re: How to import multiple excel files using Macros's language

RW9 — Tue, 04 Jul 2017 14:12:46 GMT

Yes, just missing a double quote to end filename (after .xlsx):

data _null_;
  do i=0 to 2;
    import_date=put(intnx("month",today()-2,i),yymm7.);
    call execute(cats('proc import datafile="C:\Users\VFMarwa\Desktop\Perso\MarwaPerso\Marwa\outils_rouges_',import_date,'.xlsx" out=import',put(i,1.),' replace; run;'));
  end;
run;

Note I use the code window to post code - keeps formatting and makes it easier to read - its the {i} above post area.

Re: How to import multiple excel files using Macros's language

Marwa_Se — Tue, 04 Jul 2017 14:14:26 GMT

@RW9 i corrected the path, i added a quotation mark after the (.xlsx') it worked, but i obtained another error message:

the file format it's not correct,

i tried to excute only this code

Data date;

do i=0 to 2;

import_date=put(intnx("month",today()-2,i),yymm7.);

end;

run;

the date format wasn't correct ( for i=3 import_date=2017M09 ) yet i need to keep the format below(Fichier_Month_Year.xlsx)

Do u have any idea?

Thank you again ..

Re: How to import multiple excel files using Macros's language

RW9 — Tue, 04 Jul 2017 14:21:26 GMT

Yes, you need to change the line:

import_date=put(intnx("month",today()-2,i),yymm7.);

To match what your filename is, so maybe:

import_date=catx("_",put(month(intnx("month",today()-2,i)),z2.),put(year(intnx("month",today()-2,i)),z4.));

This should create immport_date variable as MM_YYYY to be concatenated in the call execute.

Re: How to import multiple excel files using Macros's language

Marwa_Se — Tue, 04 Jul 2017 14:45:34 GMT

It works good!! you're really a genius 😄 Only one thing "i" must starts from -3 to -1 in order to obtain the last 3 months

so (do i= -3 to -1;) instead of (do i=0 to 2;)

Thank you so much for your help you saved my life 😄