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 🙂
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 😄
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.
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
----
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 ...
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.
@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 ..
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.
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 😄
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.
@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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.