DATA Step, Macro, Functions and more

How to import multiple excel files using Macros's language

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How to import multiple excel files using Macros's language

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.

-

22

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.

-

76

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 Smiley Happy


Accepted Solutions
Solution
‎07-04-2017 10:45 AM
Occasional Contributor
Posts: 9

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

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

 

so (do i= -3 to -1Smiley Wink instead of (do i=0 to 2Smiley Wink

 

Thank you so much for your help you saved my life Smiley Very Happy

View solution in original post


All Replies
Super User
Super User
Posts: 7,988

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

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.

Occasional Contributor
Posts: 9

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

Thank you for your quickly answer Smiley Happy 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 Smiley Embarassed

 

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

----

22

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

 

Super User
Super User
Posts: 7,988

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

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.

Occasional Contributor
Posts: 9

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

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

Super User
Super User
Posts: 7,988

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

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.

Solution
‎07-04-2017 10:45 AM
Occasional Contributor
Posts: 9

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

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

 

so (do i= -3 to -1Smiley Wink instead of (do i=0 to 2Smiley Wink

 

Thank you so much for your help you saved my life Smiley Very Happy

Super User
Posts: 7,854

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

[ Edited ]

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.

 

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

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

Posted in reply to KurtBremser

@KurtBremser 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 Smiley Sad

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 212 views
  • 4 likes
  • 3 in conversation