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

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Marwa_Se
Obsidian | Level 7

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 😄

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Marwa_Se
Obsidian | Level 7

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Marwa_Se
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Marwa_Se
Obsidian | Level 7

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 😄

Kurt_Bremser
Super User

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.

 

Marwa_Se
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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