英文:
There are two tables having 7 columns with 6 of char type and one of num type. Is there a way to find the added deleted and modifed rows?
问题
以下是翻译好的部分：
这里有7列。这些列对于表A和表B都是通用的：
`模式，表名，列名，类型，长度，标签，非空`
表A的值为：
模式|表名|列名|类型|长度|标签|非空
---|---|---|---|---|---|---
sch1|instance|cntrycd|num|124|code|no'
sch1|validate|validation|char|123|rules|Yes'
表B的值为：
模式|表名|列名|类型|长度|标签|非空
---|---|---|---|---|---|---
sch2|procedure|steps|num|123|Rules|Yes
sch2|validate|validation|char|124|rules|No
期望的输出是（以表格格式显示）：
模式|表名|列名|类型|长度|标签|非空|操作
---|---|---|---|---|---|---|---
sch1|instance|cntrycd|num|124|code|no| --> 已删除
sch2|procedure|steps|num|123|Rules|Yes| --> 已添加
sch2|validate|validation|char|124|rules|No| --> 已修改*
* 由于有一些列发生了修改，例如：此记录的长度和非空列已修改）
如果发生修改，即使其中一列发生更改，其他所有记录仍保持不变，也应将其标记为已修改。
无法获取所需的输出。
英文:
The 7 columns are as follows. These columns are common for both table A and table B:
Schema, tablename, columnname, type, length, label, notnull
Table A has values:
| Schema | tablename | columnname | type | length | label | notnull |
|---|---|---|---|---|---|---|
| sch1 | instance | cntrycd | num | 124 | code | no' |
| sch1 | validate | validation | char | 123 | rules | Yes' |
Table B has values
| Schema | tablename | columnname | type | length | label | notnull |
|---|---|---|---|---|---|---|
| sch2 | procedure | steps | num | 123 | Rules | Yes |
| sch2 | validate | validation | char | 124 | rules | No |
The output that is expected is (in a tabular format)
| Schema | tablename | columnname | type | length | label | notnull | Action |
|---|---|---|---|---|---|---|---|
| sch1 | instance | cntrycd | num | 124 | code | no | --> Deleted |
| sch2 | procedure | steps | num | 123 | Rules | Yes | --> Added |
| sch2 | validate | validation | char | 124 | rules | No | --> Modified* |
* As there were few of the columns that got modified example: Length and notnull columns for this record is modified)
In case of modified even if in one of the columns is changed and rest all the records remain the same then it should flag it as modified.
Unable to get the desired output.
答案1
得分: 1
前提是记录通过tablename, columnname唯一键进行标识。
Proc COMPARE 是一种有效的工具，用于发现和评估两个带有键的数据集之间的差异。还有许多其他技术：
- DATA; MODIFY
- DATA; MERGE
- DATA; hash()
- SQL; FULL JOIN
示例：
proc sql;
create table one (
Schema char(10)
, tablename char(32)
, columnname char(32)
, type char(4)
, length num
, label char(200)
, notnull char(3)
);
create table two like one;
insert into one
values ('sch1','instance','cntrycd','num',124,'code','no')
values ('sch1','validate','validation','char',123,'rules','Yes')
;
insert into two
values ('sch2','procedure','steps','num',123,'Rules','Yes')
values ('sch2','validate','validation','char',124,'rulez','No')
;
proc compare noprint base=one compare=two outall
out=comparison
;
id tablename columnname;
var type length label notnull;
run;
请注意，我保留了代码的原始格式。
英文:
Presuming the records are uniquely keyed by tablename, columnname
Proc COMPARE is an effective too for discovering and evaluating the differences between two keyed data sets. There are numerous other techniques as well:
- DATA; MODIFY
- DATA; MERGE
- DATA; hash()
- SQL; FULL JOIN
Example:
proc sql;
create table one (
Schema char(10)
, tablename char(32)
, columnname char(32)
, type char(4)
, length num
, label char(200)
, notnull char(3)
);
create table two like one;
insert into one
values ('sch1','instance','cntrycd','num',124,'code','no')
values ('sch1','validate','validation','char',123,'rules','Yes')
;
insert into two
values ('sch2','procedure','steps','num',123,'Rules','Yes')
values ('sch2','validate','validation','char',124,'rulez','No')
;
proc compare noprint base=one compare=two outall
out=comparison
;
id tablename columnname;
var type length label notnull;
run;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论