Dear Tom, Thanks a lot for your suggestions and your explaination. That is very helpful. However, I still got the following error after using the code that you offered. I also checked the temporary data files that should be created, but neither sashelp.out_adc20141 nor the sashelp.out_adc20134 was found. I also found that the data file work.check has no observations. I am wondering whether the code created the file of out_result, I did not find it in the the library of sashelp. I am not positive. Best wishes, 1195
1196
1197 /*Comparing data set schemas in SAS*/
1198 /*Selecting Data from More than One Table by Using Joins*/
1199
1200
1201 *libname ab "c:\ab";
1202 *libname xy "c:\xy";
1203
1204
1205
1206 /*proc contents data=yl.out_adc20141;run;
1207 proc contents data=ab.out_adc20134;run;
1208 proc contents data=YL.out_adc20181;run;*/
1209
1210 /* Macro to check attributes of input data against reference */
1211 /* data, and create a ranked list of data integrity exceptions */
1212
1213 libname yl "Z:\Yanling\Test_Data";
NOTE: Libref YL was successfully assigned as follows:
Engine: V9
Physical Name: Z:\Yanling\Test_Data
1214 libname ab "C:\Tasks\My tasks\13 week 9.6.17";
NOTE: Libref AB was successfully assigned as follows:
Engine: V9
Physical Name: C:\Tasks\My tasks\13 week 9.6.17
1215 %macro check_reference_data(
1216 ab /* location of reference data */,
1217 out_adc20134 /* name of reference table for comparison */,
1218 yl /* location of input data to compare */,
1219 out_adc20141 /* name of input table to compare */,
1220 out_result /* new data set to hold result */
1221 );
1222 proc sql;
1223
1224 create view WORK._TMPIN as
1225 select * from &yl..&out_adc20141.;
1226 create table &out_result. as
1227 select
1228 "&out_adc20141." as DATASET,
1229 "&out_adc20134." as REFERENCE,
1230 a.name as ref_name, b.name,
1231 a.length as ref_length, b.length,
1232 a.type as ref_type, b.type,
1233 a.format as ref_format, b.format,
1234 case
1235 when b.name is missing then
1236 'SEV-1: MISSING COLUMN'
1237 when a.name is missing then
1238 'SEV-4: EXTRA COLUMN'
1239 when b.type <> a.type then
1240 'SEV-1: MISMATCHED TYPE'
1241 when b.length > a.length then
1242 'SEV-2: MISMATCHED LENGTH, POSSIBLE TRUNCATION'
1243 when b.format <> a.format then
1244 'SEV-3: MISMATCHED FORMAT, POSSIBLE MISINTERPRETATION'
1245 else
1246 'OK'
1247 end as RULE
1248 from
1249 ((select name, length, type, format from sashelp.vcolumn
1250 where libname=upcase("&ab") and memname=upcase("&out_adc20134"))
1251
1252 /*where libname="&ab" and memname="&out_adc20134"*/)a FULL JOIN
1253 (select name, length, type, format from sashelp.vcolumn
1254 where libname="WORK" and memname="_TMPIN") b on (upcase(a.name)=upcase(b.name))
1255 order by RULE;
1256
1257 drop view work._tmpIn;
1258 quit;
1259 %mend; /* check_reference_data */
1260
1261 /*%check_reference_data(sashelp,out_adc20134,sashelp,out_adc20141,work.check);
1262 title "Does ADC20141 have everything that ADC20134 has? Yes, and more";
1263 proc print data=work.check;
1264 run;*/
1265
1266
1267
1268 %check_reference_data
1269 (ab=sashelp
1270 ,out_adc20134=out_adc20134
1271 ,yl=sashelp
1272 ,out_adc20141=out_adc20141
1273 ,out_result=work.check
1274 )
NOTE: SQL view WORK._TMPIN has been defined.
NOTE: The "<>" operator is interpreted as "not equals".
ERROR: File SASHELP.OUT_ADC20141.DATA does not exist.
NOTE: Table WORK.CHECK created, with 0 rows and 11 columns.
NOTE: View WORK._TMPIN has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
1275 proc print data=work.check;
1276 run;
NOTE: No observations in data set WORK.CHECK.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
... View more