Discussion :

[Clifabs]

Bonjour,

J'ai trouvé cette macro dans le livre de «SAS MACRO LANGUAGE 2». Elle permet de lire plusieurs fichiers EXCEL dans un sous-répertoire. Je ne sais pas s'il contient une erreur, mais elle refuse de fonctionner. Je n'ai rien modifié de la version originale.

Merci de votre aide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
%macro readxls(dir);
   %local fileref rc did dnum dmem memname len dsn didc;
   %let rc=%sysfunc(filename(fileref,&dir));
   %let did=%sysfunc(dopen(&fileref));
   %if &did=0 %then %do;
      %put ERROR: Directory does not exist;
	  %put ERROR- The macro will terminate.;
      %return;
   %end;
   %let dnum=%sysfunc(dnum(&did));
   %do dmem=1 %to &dnum;
      %let memname=%sysfunc(dread(&did,&dmem));
      %if %upcase(%scan(&memname,-1,.)) = XLS %then
         %do;
            libname workbook "&dir\&memname";
            data null;
               set sashelp.vtable end=last;
               where libname = "WORKBOOK";
               call symputx(cats('sheet', _n_), memname,'L'); 
               if last then call symputx('n', _n_,'L');
            run;
            %do i=1 %to &n;
	           %let len=%eval(%length(&&sheet&i)-1);
	           %let dsn=%substr(&&sheet&i,1,&len);
	           data work.&dsn;
                  set workbook."&&sheet&i"n;
               run;
               proc print data=work.&dsn;
			      title "Data Imported From &dir\&memname";
               run;
            %end;
            libname workbook clear; 
         %end;
      %else %if %scan(&memname,2,.)=  %then %readxls(&dir\&memname);
   %end;
   %let didc=%sysfunc(dclose(&did));
   %let rc=%sysfunc(filename(fileref));
%mend readxls;
 
options mlogic;
%readxls(s:\workshop\c3)
 
title;
options nomlogic;

[s_a_m]

c'est quoi qui ne marche pas ?
il te dis quoi dans la log?
et tu veux faire quoi? importer plusieurs fichiers de même structure du même sous répertoire?

[bahraoui]

bonjour,

Je viens de tester cette macro, et elle marche très bien.
elle importe des fichiers Excel qui se trouve dans un répertoire; le nom de la table SAS correspond au nom de l'onglet du fichier excel importé.

Cordialement

[s_a_m]

Idem pour moi ;

[bahraoui]

Si un fichier excel contient plusieurs onglet => autant de table SAS
il est bien fait ce programme

[Manoutz]

explique nous ce que tu as fait et quel est le message dans ta log!

[Clifabs]

Merci pour vos réponses. Voici une partie de mon log. J'ai mis aussi le répertoire que j'ai mis pour appeler la macro.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
%readxls(\\Natn4\nat_shrdata\MAC\hfrancois\Neighbourhood Project)

LOG

MLOGIC(READXLS):  %IF condition %upcase(%scan(&memname,-1,.)) = XLS is FALSE
MLOGIC(READXLS):  %IF condition %scan(&memname,2,.)= is FALSE
MLOGIC(READXLS):  %DO loop index variable DMEM is now 9; loop will iterate again.
MLOGIC(READXLS):  %LET (variable name is MEMNAME)
MLOGIC(READXLS):  %IF condition %upcase(%scan(&memname,-1,.)) = XLS is FALSE
MLOGIC(READXLS):  %IF condition %scan(&memname,2,.)= is FALSE
MLOGIC(READXLS):  %DO loop index variable DMEM is now 10; loop will iterate again.
MLOGIC(READXLS):  %LET (variable name is MEMNAME)
MLOGIC(READXLS):  %IF condition %upcase(%scan(&memname,-1,.)) = XLS is TRUE
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

NOTE: Data file MFCLAIMS.EQY2010.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MFCLAIMS.EQY2011.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.CONTACTS.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.FMTLIB.DATA is in a format that is native to another host, or the file encoding does not match the session 
      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.FORMATS.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.INS_20084.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.INS_20085.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.INS_20086.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.MLS_PROVINCE.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.MLS_PROVINCE_GAMME.DATA is in a format that is native to another host, or the file encoding does not match 
      the session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might 
      reduce performance.
NOTE: Data file MAC_DATA.RMSS1008.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.SCSSPLIM.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.SHS02.DATA is in a format that is native to another host, or the file encoding does not match the session 
      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.SHS03.DATA is in a format that is native to another host, or the file encoding does not match the session 
      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.SHS04.DATA is in a format that is native to another host, or the file encoding does not match the session 
      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.SHS05.DATA is in a format that is native to another host, or the file encoding does not match the session 
6                                                          The SAS System                            11:41 Wednesday, April 20, 2011

      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Data file MAC_DATA.SHS06.DATA is in a format that is native to another host, or the file encoding does not match the session 
      encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
      WHERE libname='WORKBOOK';
NOTE: The data set WORK.NULL has 0 observations and 41 variables.
NOTE: DATA statement used (Total process time):
      real time           1.51 seconds
      cpu time            0.87 seconds
      

WARNING: Apparent symbolic reference N not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 
       &n 
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro READXLS will stop executing.

[MEGAMIND2]

ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

Tu as peut être pas le module approprié, tu me diras si celle là fonctionne, je l'ai bidouillé vite fait.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
options mprint mlogic symbolgen;
 
%MACRO IMPORT(dir=);
 
DATA fichiersXLS ;
	LENGTH fic $ 1000;
	INFILE "&dir.\*.xls" FILENAME = fic ;
	INPUT ;
	fichier = fic ;
RUN ;
 
DATA fichiersXLS ;
	ATTRIB nom_fichier FORMAT=$1000.;
	SET fichiersXLS ;
	nom_fichier=TRANWRD(TRANWRD(scan(fichier,-1,'\'),'.xls', ' '),',', '.');
	fichier=quote(compress(fichier));
RUN;
 
DATA _NULL_;
	SET fichiersXLS;
	CALL SYMPUT("NB",_N_);
	CALL SYMPUT("nom_fichier"!!LEFT(_N_),nom_fichier);
	CALL SYMPUT("rep"!!LEFT(_N_),fichier);
RUN;
 
%DO i=1 %TO &NB;
	PROC IMPORT DATAFILE =&&rep&i.	
		OUT = &&nom_fichier&i.	replace				
		DBMS = EXCEL2000 ;						
		MIXED = YES ; 								
		GETNAMES = YES;						
	RUN ;
 
%END;
 
%MEND;
%IMPORT(dir=);

ps: ton fichier doit contenir qu'un seul onglet

[Clifabs]

Merci MEGAMIND2.

Ton programme fonctionne très bien. Je penses que je vais laisser tomber le mien et utliser le tien.

Encore une fois merci

[bahraoui]

Tu es en SAS 8.2?
Voila le code qui génère l"erreur : libname workbook "&dir\&memname";
Le programme crée des libname sur des fichiers Excel.