1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
%macro tt();
data _null_;
set sashelp.class;
call symputx ('name'||left(_n_),catt(name,';') );
call symput ('nbr',_n_);
run;


%do i=1 %to &nbr;
%global  nname&i;
%let nname&i=%SUPERQ(name&i);

%end
%end;
%mend;

%tt;

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
data tab1;
input id x1 x2 x3 x4 x5;
cards;
1 3 2 2 . 1
1 . 1 1 3 2
1 9 . 3 9 6
1 8 . 7 3 2
1 8 2 . 6 .
1 6 5 . . 9
2 1 3 . 8 . 
2 2 5 8 . .
2 3 3 11 . .
2 4 3 9 . 10
;
run;


/*Etape 1*/

data tab2(drop=rc i j);
set tab1;
array X(*) x:;
do i=1 to dim(x);
   if x(i)=. then do;
   rc=i;
   do j=rc to dim(x);
   if x(j) ne . then do;
   x(i)=x(j);
   x(j)=.;
   leave;
 
   end;  
  end;
 end;
end;
run;

/*Etape 2*/

ods select none;
ods output nlevels=tab3;
proc freq data=tab2 nlevels;
 tables _all_;
run;
proc sql;
 select tablevar into : drop separated by ','
  from tab3
   where NNonMissLevels=0;
   
  alter table tab2
   drop &drop; 
quit;

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
 
data test;
 input @1 id $3.
 @5 var $5.;
datalines;
001 acbf
002 abxde
003 12cce
004 abc e
; run;
 
data test1(drop=i ver diff_car
           rename=(diff_car1=diff_car));
		   retain id var ref diff_car;
set test;
length diff_car1 $10.;
retain diff_car1;
by id;
if first.id then do;
diff_car1="";
end;
do i=1 to length(var);
ref='abc';
diff_car=char(var,i);
ver=verify(diff_car,ref);
if diff_car=' ' then diff_car="~";
if ver=1 then diff_car1=catx(',',diff_car1,diff_car);
end ;
run;

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
%macro modif(rep=,type=,date=);

PROC FORMAT;
VALUE $mois
'janvier'=01
'fevrier'=02
'mars'=03
'avril'=04
'mai'=05
'juin'=06
'juillet'=07
'août'=08
'septembre'=09
'octobre'=10
'novembre'=11
'decembre'=12;
run;


DATA test  (keep=name date_mod cr_date Bytes);
rc=filename ("rep","&rep");

   dirid = DOPEN("rep") ;
	
         	DO i=1 TO DNUM(dirId) ;
	    
		  rc = FILENAME("fic", TRIM("&rep.\")!!LEFT(DREAD(dirid,i))) ;
                  name=dread(dirid,i);
		          
                        if index( name, "&type.") then do ;
                          fid = FOPEN("fic") ;
	                     date=finfo(fid,'Last Modified');
		             LastModJ=input(SCAN(Date ,1," "),best.);
                             mois=SCAN(Date ,2," ");
                             LastModM=input( put(mois,mois.),best.);
                             LastModA=input(SCAN(Date ,3," "),best.) ;
		             date =mdy(LastModM,LastModJ,LastModA);
		             datelast=input(compress(date),best.);
			     date_mod=put(datelast,date9.);
		
		                   if datelast>="&date."d then do;
                                      Bytes=finfo(fid,'File Size (bytes)');                                                                                  
                                      cr_date=finfo(fid,'Create Time');
		                      output;
          	                   end;
                        fid = fclose( fid ) ;
                   END ;
	 END;
    rc = DCLOSE(dirId) ;
RUN ; 
filename rep clear;

%mend;

*/Exemple/*
%modif(rep=C:\temp,type=txt,date=01aug2015);

1
2
3
4
5
6
7
8
9
10
11
12
13
14

%macro pipe(cmd);

filename tmp pipe &cmd;

data want ;

  infile tmp   firstobs=2;
  input var1 $ var2 $ ;
run;
%mend;

%pipe('FOR /F "tokens=1,2 delims=, " %i in (c:\sas\fichier.csv) do @echo %i %j');

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
/*Creation d'une archive*/

/*ods tagsets excelxp*/

%let date=&sysdate;

ods package open nopf;

   ods tagsets.excelxp 
   style=styles.sasweb 
   file=" c:\sas\BASEUL&sysdate..xls" package;

ods listing close;

ods tagsets.excelxp options (sheet_name = ' apprentis ' sheet_interval="none" autofit_height = 'yes' );
proc report data=sashelp.class nowd
style(column) =	[cellwidth = 2.5 cm
just = c
vjust = m]
style (header) =	[just = c
vjust = m ];
title ;
column name age sex weight height;
define name / "name" style(column)= [cellwidth = 2cm]; ;
define age / " age " style(column)= [cellwidth = 5cm];
define sex / "Sex" ;
define weight / "weight" ;
define height / "height" ;
 
run; 
 
ods tagsets.excelxp close;
ods package publish archive properties (archive_name="dossier.zip"
archive_path="c:\sas\"); 
ods package close clear;


/*Ods html*/

ods listing close;
goptions dev=gif xpixels=480 ypixels=320;
	
ods package open;
ods html package;

proc gplot data=sashelp.class;
     plot height*weight;
     by name;
 run;
 quit;    

ods html close;
ods package publish archive properties(archive_name="SimpleExample.zip" archive_path="c:\sas\");
ods package close;

ods listing;
 


/*Création archive en ajoutant fichiers*/

filename newtxt "c:\sas\dossier\test.txt";
filename newexcel "c:\sas\dossier\test.xls";
filename new "c:\sas\dossier\report.xls";
ods package(newzip) open nopf;
ods package(newzip) add file=newexcel path="Excel/";
ods package(newzip) add file=new path="Excel/";
ods package(newzip) add file=newtxt path="txt/";
ods package(newzip) publish archive 
  properties(
   archive_name="test.zip" 
   archive_path="c:\sas\dossier\"
  );
ods package(newzip) 

close;

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

/*Mise à jour d'un fichier Excel zippé*/



%macro package(name,rep);


/*Extraction de données */

%sysexec "C:\Program Files (x86)\WinRAR\WinRAR.exe" e "&rep.\test.zip" "&rep." -r;

 data _null_;
 x=sleep(5);
 run;


/*Table mise à jour*/

data class;
set sashelp.class;
if _n_ in (1,2,3);
run;
 

proc export data =class
 OUTFILE= "&rep.\&name..xls"
 DBMS=EXCEL REPLACE;
 SHEET="test";
 run;


/*Mise à jour du package*/

filename &name "&rep.\&name..xls";
ods package(newzip) open nopf;
ods package(newzip) add file=&name path="Excel/";
ods package(newzip) publish archive 
  properties(
   archive_name="test.zip" 
   archive_path="&rep."
  );
ods package(newzip) 

close;

option noxwait xsync;


%sysexec DEL "&rep.\&name..xls" ;
 
FILENAME &name. CLEAR ;

%mend;

%package(test,c:\sas\dossier);

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
option symbolgen mprint;

%macro optimisation(lib=);

proc contents data=&lib.._all_ out=tt.tables (keep=memname)  noprint;
run;

proc sort data=tt.tables nodup; by memname; run;

%macro opt(memname=);
proc contents data=&lib..&memname. out=tt.tt1 noprint;
run;

data tt.tt1 (where=(type=2));
set tt.tt1;
run;

data _null_;
set tt.tt1 end=last;
call symputx ('nn'||left(_n_),name);
IF last then call symputx('cnt',_n_);
run;

data _null_;
%do i=1 %to &cnt;
  %global val&i;
  proc sql;
  select max(length(&&nn&i)) into: val&i from &lib..&memname.;
  quit;
 %end; 
  run;
  data _null_;
  %do i=1 %to &cnt;
  proc sql noprint;
  alter table &lib..&memname.
  modify &&nn&i char(&&val&i.) format=$%sysfunc(trim(&&val&i.)). informat=$%sysfunc(trim(&&val&i.)). ;
  %end;
  run;

%mend;

data _null_;
set tt.tables;
call execute('%nrstr(%opt(memname='||strip(memname)||'));');
run;
%mend;

%optimisation(lib=work);

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
libname sasref 'C:/sas';

options sasmstore=sasref mstored;

%macro operation() /store;
 %global mv_ope;
 %window w_saisie color=cyan COLUMNS=40 rows=12 icolumn=30 irow=15
    #2 @5 "Opération souhaitée  :" @30 mv_ope 100 required=yes;
    %display w_saisie;
 run;
%mend;

%macro saisi_import() /store;
 %global mv_DATAFILE mv_out mv_sheet;
 %window w_saisie color=cyan COLUMNS=100 rows=12 icolumn=30 irow=15
    #2 @1 "path  :" @10 mv_DATAFILE 100 required=yes
	#5 @1 "table  :" @10 mv_out 100 required=yes
	#7 @1 "Feuile  :" @10 mv_sheet 100 required=yes;
    %display w_saisie;
 run;
%mend;

%macro saisi_export() /store;
 %global mv_data mv_file mv_sheet;
 %window w_saisie color=cyan COLUMNS=100 rows=12 icolumn=30 irow=15
    #2 @1 "table:" @10 mv_data  100 required=yes
	#5 @1 "chemin  :" @10 mv_file 100 required=yes
	#7 @1 "Feuile  :" @10 mv_sheet 100 required=yes;
    %display w_saisie;
 run;
%mend;

%macro saisi_tri() /store;
 %global mv_data mv_out mv_by;
 %window w_saisie color=cyan COLUMNS=100 rows=12 icolumn=30 irow=15
    #2 @1 "table_ent  :"    @15 mv_data 100 required=yes
	#5 @1 "table_sor:"      @15 mv_out 100 required=yes
	#7 @1 "by  :"           @10 mv_by 100 required=yes;
    %display w_saisie;
 run;
%mend;


%macro Choix() /store;
%if &mv_ope=import  %then %do;

%saisi_import;

PROC IMPORT OUT=&mv_out
	DATAFILE="&mv_path"
	DBMS=EXCEL REPLACE;
	SHEET="&mv_sheet";
RUN;

%end;

%if &mv_ope=export  %then %do;

%saisi_export;

proc export data =&mv_data
 OUTFILE= "&mv_file"
 DBMS=EXCEL REPLACE;
 SHEET="&mv_sheet";
 run;
 %end;
%if &mv_ope=tri  %then %do;

%saisi_tri;
PROC sort data=&mv_data out=&mv_out ; by &mv_by ;RUN;
%end;
%mend;

libname sasref 'C:/sas';
OPTIONS MSTORED SASMSTORE = sasref ;

%operation;

%choix;