topic Re: SAS Dataset in SAS Programming

SAS Dataset

JJP1 — Thu, 21 Nov 2019 10:11:58 GMT

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

Re: SAS Dataset

Kurt_Bremser — Thu, 21 Nov 2019 10:22:58 GMT

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

Re: SAS Dataset

Jagadishkatam — Thu, 21 Nov 2019 10:54:34 GMT

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;

Re: SAS Dataset

JJP1 — Thu, 21 Nov 2019 12:52:22 GMT

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 .

Re: SAS Dataset

Kurt_Bremser — Thu, 21 Nov 2019 13:00:52 GMT

Maxim 14: Use the Right Tool. In SQL, this is update.