Loading...
Searching...
No Matches
loadfile.sas
Go to the documentation of this file.
1/**
2 @file loadfile.sas
3 @brief Loads a file
4 @details
5
6 <h4> SAS Macros </h4>
7 @li mddl_sas_cntlout.sas
8 @li mp_abort.sas
9 @li mf_getplatform.sas
10 @li mf_getuser.sas
11 @li mf_getvarlist.sas
12 @li mf_mkdir.sas
13 @li mf_verifymacvars.sas
14 @li mf_wordsinstr1butnotstr2.sas
15 @li dc_assignlib.sas
16 @li mpe_getgroups.sas
17 @li mp_lockfilecheck.sas
18 @li mpe_loader.sas
19 @li mp_cleancsv.sas
20 @li mp_binarycopy.sas
21 @li mpeinit.sas
22
23 @version 9.2
24 @author 4GL Apps Ltd
25 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
26 and may not be re-distributed or re-sold without the express permission of
27 4GL Apps Ltd.
28
29**/
30
31%global table dlm;
32%mpeinit(fetch=NO)
33
34%global _WEBIN_FILENAME1 _WEBIN_FILENAME2
35 _WEBIN_FILEREF _WEBIN_FILEREF1 _WEBIN_FILEREF2;
36%macro load();
37%if %mf_getplatform()=SASVIYA %then %do;
38 %global _webin_fileuri _webin_fileuri1 _webin_fileuri2;
39 %let _webin_fileuri1=%sysfunc(coalescec(&_webin_fileuri1,&_webin_fileuri));
40 %if "&_webin_fileuri1" ne "" %then %do;
41 %put &=_webin_fileuri1;
42 filename sjfref1 filesrvc "&_webin_fileuri1";
43 %let _WEBIN_FILEREF1=sjfref1;
44 %end;
45 %if "&_webin_fileuri2" ne "" %then %do;
46 %put &=_webin_fileuri2;
47 filename sjfref2 filesrvc "&_webin_fileuri2";
48 %let _WEBIN_FILEREF2=sjfref2;
49 %end;
50%end;
51%mend load;
52%load()
53
54%let _WEBIN_FILENAME1=%sysfunc(coalescec(&_WEBIN_FILENAME1,&_WEBIN_FILENAME));
55%let _WEBIN_FILEREF1=%sysfunc(coalescec(&_WEBIN_FILEREF1,&_WEBIN_FILEREF));
56
57%let abort=0;
58/* we do not know if the excel file will be first or second fileref */
59data _null_;
60 ext1=upcase(scan(symget('_WEBIN_FILENAME1'),-1,'.'));
61 ext2=upcase(scan(symget('_WEBIN_FILENAME2'),-1,'.'));
62 if ext1='CSV' then do;
63 csvname=symget('_WEBIN_FILENAME1');
64 csvref=symget('_WEBIN_FILEREF1');
65 xlsname=symget('_WEBIN_FILENAME2');
66 xlsref=symget('_WEBIN_FILEREF2');
67 end;
68 else if ext2='CSV' then do;
69 csvname=symget('_WEBIN_FILENAME2');
70 csvref=symget('_WEBIN_FILEREF2');
71 xlsname=symget('_WEBIN_FILENAME1');
72 xlsref=symget('_WEBIN_FILEREF1');
73 end;
74 else call symputx('abort',1);
75
76 call symputx('csvname',csvname);
77 call symputx('csvref',csvref);
78 call symputx('xlsname',xlsname);
79 call symputx('xlsref',coalescec(xlsref,'0'));
80run;
81
82%mp_abort(iftrue= (&abort=1)
83 ,mac=&_program
84 ,msg=%str(File "&csvname" or "&xlsname" must be a CSV!
85 (Comma separated with .csv extension))
86)
87
88
89%let user=%mf_getuser();
90
91%mp_abort(
92 iftrue=(%mf_verifymacvars(table)=0)
93 ,mac=&_program
94 ,msg=%str(Missing: table)
95)
96
97%let table=%upcase(%trim(&table));
98
99/* load parameters */
100data _null_;
101 libds=upcase(symget('table'));
102 call symputx('orig_libds',libds);
103 call symputx('orig_lib',scan(libds,1,'.'));
104 call symputx('orig_ds',scan(libds,2,'.'));
105 is_fmt=0;
106 if substr(cats(reverse(libds)),1,3)=:'CF-' then do;
107 libds=scan(libds,1,'-');
108 putlog "Format Catalog Captured";
109 libds='work.fmtextract';
110 call symputx('libds',libds);
111 call execute('%mddl_sas_cntlout(libds=work.fmtextract)');
112 is_fmt=1;
113 end;
114 else call symputx('libds',libds);
115 call symputx('is_fmt',is_fmt);
116 putlog (_all_)(=);
117run;
118
119/* check that the user has the requisite access */
120%mpe_getgroups(user=&user,outds=groups)
121
122proc sql;
123create table accesscheck as
124select * from groups
125 where groupname="&mpeadmins"
126 or groupname in (select sas_group from &mpelib..mpe_security
127 where &dc_dttmtfmt. lt tx_to
128 and access_level="EDIT"
129 and (
130 (libref="&orig_lib" and dsn="&orig_ds")
131 or (libref="&orig_lib" and dsn="*ALL*")
132 or (libref="*ALL*" and dsn="*ALL*")
133 or (libref="*ALL*" and dsn="&orig_ds")
134 ));
135%let nobs=;
136select count(*) into: nobs from &syslast;
137%mp_abort(iftrue= (&nobs=0)
138 ,mac=&sysmacroname
139 ,msg=%str(&user not authorised to load &orig_libds per &mpelib..mpe_security)
140)
141
142%dc_assignlib(WRITE,&orig_lib)
143
144%mp_abort(iftrue= (&syscc ge 4)
145 ,mac=loadfile
146 ,msg=%str(Issue assigning library &orig_lib)
147)
148
149%global txfrom txto processed rk;
150
151data _null_;
152 set &mpelib..MPE_TABLES;
153 where libref="&orig_lib" and dsn="&orig_ds";
154 call symputx('txfrom',var_txfrom);
155 call symputx('txto',var_txto);
156 call symputx('processed',var_processed);
157 if not missing(RK_UNDERLYING) then call symputx('rk',buskey);
158run;
159
160%mp_lockfilecheck(libds=&orig_libds)
161
162data compare;
163 set &libds(drop=&txfrom &txto &processed &rk);
164 stop;
165run;
166
167%mp_abort(iftrue= (&syscc ne 0)
168 ,mac=&_program..sas
169 ,msg=%str(syscc=&syscc line 80)
170)
171
172/* get line terminator, assume it's the first cr, lf, or crlf */
173data _null_;
174 length text $32767 term $4;
175 call missing (of _all_);
176 fid=fopen("&csvref",'I',32767,'b');
177 rc=fread(fid);
178 rc2=fget(fid,text,32767);
179 cr=find(text,'0D'x );
180 lf=find(text,'0A'x );
181 crlf=find(text,'0D0A'x);
182 rc=fclose(fid);
183 if crlf>0 & cr<crlf then term='CR';
184 else if crlf>0 & crlf<lf then term='CRLF';
185 else if lf>0 & cr>0 & lf<cr then term='LF';
186 else if lf>0 then term='LF';
187 else term='CR';
188 call symputx('termstr',term);
189run;
190
191data _null_;
192 infile &csvref lrecl=32000 dsd termstr=&termstr;
193 input;
194 length incols_unsorted $32000 dlm $1;
195 incols_unsorted=compress(upcase(_infile_),"'"!!'"');
196 /* dlm has length 1 so will be the first non alpha / digit char */
197 /* expectation is that there will not be any crazy characters in first col! */
198 dlm=compress(incols_unsorted,'_ ','ad');
199 incols_unsorted=compress(incols_unsorted,dlm!!'_','kado');
200 incols_unsorted=tranwrd(incols_unsorted,dlm,' ');
201 call symputx('incols_unsorted',incols_unsorted);
202 call symputx('dlm',dlm);
203 putlog incols_unsorted=;
204 putlog dlm=;
205 stop;
206run;
207
208%mp_abort(iftrue= (&syscc ne 0)
209 ,mac=&_program..sas
210 ,msg=%str(syscc=&syscc line 99)
211)
212
213%let basecols=%upcase(%mf_getvarlist(work.compare,dlm=%str( )));
214
215%let missing_cols=%trim(
216 %mf_wordsInStr1ButNotStr2(
217 Str1=&basecols
218 ,Str2=&incols_unsorted
219 ));
220
221%let msg=
222 Expected cols: <b>&basecols</b>
223 </br>Received cols: <b>&incols_unsorted</b>
224 </br>Missing cols: <b>&missing_cols</b>
225;
226%mp_abort(iftrue= (%length(%trim(&missing_cols)) > 1 or &syscc ne 0)
227 ,mac=mpestp_loadfile.sas
228 ,msg=%superq(msg)
229)
230%let msg=0;
231
232
233
234PROC FORMAT;
235 picture yymmddhhmmss other='%0Y%0m%0d_%0H%0M%0S' (datatype=datetime);
236RUN;
237
238/* create a dataset key (datetime plus 6 digit random number plus PID) */
239%let mperef=DC%left(%sysfunc(datetime(),B8601DT19.3))_%substr(
240 %sysfunc(ranuni(0)),3,6)_%substr(%str(&sysjobid ),1,4);
241
242/* Create package folder and redirect the log */
243%let dir=&mpelocapprovals/&mperef;
244%mf_mkdir(&dir)
245
246/* clean embedded line breaks and force CRLF line endings */
247%mp_cleancsv(in=&csvref, out=&dir/&orig_libds..csv)
248%mp_abort(iftrue= (&syscc ne 0)
249 ,mac=&_program..sas
250 ,msg=%str(issue in mp_cleancsv)
251)
252
253%put; %put; %put log is being redirected;
254%let url=_program=%substr(&_program
255 ,1,%length(&_program)-8)getlog%nrstr(&)table=&mperef;
256%put to retrieve, visit this url:; %put;%put;
257%put &url;
258%put;
259
260/* proc printto log="&dir/weblog.txt";run; */
261libname approve "&dir";
262
263options mprint;
264%put &=mperef;
265%put &=termstr;
266%put &=dlm;
267
268%mpe_loader(mperef=&mperef
269 ,submitted_reason_txt=%quote(File upload: %superq(csvname))
270 ,dlm=%superq(dlm)
271 ,url=%superq(url)
272 ,termstr=CRLF
273 ,dc_dttmtfmt=&dc_dttmtfmt
274)
275%mp_abort(mode=INCLUDE)
276
277%mp_abort(
278 iftrue= (%sysfunc(fileexist(%sysfunc(pathname(work))/mf_abort.error)) ne 0)
279 ,mac=&_program
280 ,msg=%nrstr(Problem occurred in &sysmacroname (mf_abort.error file found))
281)
282
283%mp_abort(iftrue= (&syscc ne 0)
284 ,mac=mpestp_loadfile.sas
285 ,msg=%str(syscc=&syscc)
286)
287
288filename outref "&dir/BKP_&xlsname";
289%mp_binarycopy(iftrue=("&xlsref" ne "0"),inref=&xlsref,outref=outref)
290
291%mp_abort(iftrue= (&syscc ne 0)
292 ,mac=&sysmacroname
293 ,msg=%str(syscc=&syscc when backing up source file &xlsname)
294)
295
296data sasparams;
297 STATUS='SUCCESS';
298 DSID="&mperef";
299run;
300
301%webout(OPEN)
302%webout(OBJ,sasparams)
303%webout(CLOSE)
304
305
306%mpeterm()