Hi ,
please have a look on below sample data and please help
data source;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 89 18NOV2019 19NOV2019
;
run;
data target;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 89 18NOV2019 19NOV2019
;
run;
incorrect data is there in source dataset for date 18NOV2019
which got alreday loaded into target table.
so now the souce data will become as below.;
data source;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 54 18NOV2019 19NOV2019
;
run;
So target dataset now should show as below output(changed from 89 to 54) when i run below code;
data target;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 54 18NOV2019 19NOV2019
;
run;
I would want to do this using proc sql option.kindly help
In SQL, use update:
data target;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 89 18NOV2019 19NOV2019
;
run;
data source;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 54 18NOV2019 19NOV2019
;
run;
proc sql;
update target t
set amt = (select amt from source s where t.num = s.num and t.sourcedate = s.sourcedate)
;
quit;
proc print data=target noobs;
run;
Result:
num amt sourcedate loaddate 1 32 19NOV2019 20NOV2019 2 45 19NOV2019 20NOV2019 3 54 18NOV2019 19NOV2019
Thanks @Kurt_Bremser .
Would you please suggest whether we can do it using proc sql (like left join ,right join,inner join) and get the same target table desired ?.
As i would want to see in this way just to see any possiblity .
Maxim 14: Use the Right Tool. In SQL, this is update.
Alternatively try the merge step to overwrite the target values in amt variable with source dataset amt variable
data target;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 89 18NOV2019 19NOV2019
;
run;
data source;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 54 18NOV2019 19NOV2019
;
run;
proc sort data=target;
by num sourcedate loaddate;
run;
proc sort data=source;
by num sourcedate loaddate;
run;
data want;
merge target(in=a) source(in=b);
by num sourcedate loaddate;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: