topic Re: How to improve my code in SAS Programming

How to improve my code

shlomiohana — Sat, 19 Oct 2024 18:56:01 GMT

Hello,

I wrote the following code, I would appreciate it if you could improve it for a shorter and more elegant writing.

The code's goal is to find all the records in the CM_EMAIL_SEND table whose emails are not in the CM_TEST_CUSTOMERS table.
If records are found, they should be added from the CM_EMAIL_SEND table to the CM_EMAIL_CHASOM table, delete the records that were found from the CM_EMAIL_SEND table, SINUN field value should be changed to CM for records passed to the table CM_EMAIL_CHASOM.
an email will be sent saying that records have been transferred between the 2 tables.

proc sql noprint;
create table AAA as
select t1.*
from CM_EMAIL_SEND t1
left join CM_TEST_CUSTOMERS t2
on lowcase(t1.email)=lowcase(t2.email)
where t2.email is null;
select count(*) into :row_count from AAA;
quit;

%put the number: &row_count;

%MACRO test;
%IF &row_count. gt 0 %THEN
%DO;
data CM_EMAIL_CHASOM;
set CM_EMAIL_CHASOM AAA(in=new);
if new then
SINUN= 'CM';
run;

proc sql;
delete from CM_EMAIL_SEND
where email in (select email from AAA);
quit;
%END;
%MEND test;
%test;

Thanks.

Re: How to improve my code

PaigeMiller — Sat, 19 Oct 2024 20:05:28 GMT

I don't see a lot that needs to be improved here.

You can get away with not creating a macro in this case, the %IF %THEN %DO and %END is allowed in open code in many situations.

The only other thing that jumps out at me (which is really very trivial) is here:

%test;

where the semi-colon is unnecessary.

In terms of style, you want to indent the code within PROCs and within data steps properly.

Re: How to improve my code

Ksharp — Sun, 20 Oct 2024 01:53:21 GMT

Assuming I understodd what you mean.

proc sql;
create table CM_EMAIL_CHASOM_2 as
select * from CM_EMAIL_CHASOM
outer union corr
select *,'CM' as SINUN from CM_EMAIL_SEND 
 where lowcase(email) not in (select lowcase(email) from CM_TEST_CUSTOMERS);

delete from CM_EMAIL_SEND
where lowcase(email) in (
 select lowcase(email) from CM_EMAIL_SEND
  where lowcase(email) not in (select lowcase(email) from CM_TEST_CUSTOMERS)
);
quit;

Re: How to improve my code

Tom — Sun, 20 Oct 2024 02:43:39 GMT

Didn't you already ask this question??

First let's make some example datasets so we have something to program against.

data cm_email_send ;
  input email :$40. var1 ;
cards;
a@b.com 1
b@c.com 2
c@d.com 3
;

data cm_test_customers;
  input email :$40. ;
cards;
a@b.com
d@e.com
;

data cm_email_chasom;
  if 0 then set cm_email_send (obs=0);
  length SINUN $2 ;
  input email var1 sinun;
cards;
x@y.com 4 xx
;

So first thing is that PROC SQL will count for you, so no need to count twice.

And really from what you describe there is no need to make a temporary copy of the data to be moved. Just insert it directly into the target dataset. Also there is no need to define a macro run simple %IF/%THEN/%END blocks of code. You can do that now in open SAS code.

Something like this:

proc sql;
%let nobs=0;
  insert into cm_email_chasom
  select a.*,'CM' as sinun 
   from cm_email_send a
   where lowcase(a.email) not in (select lowcase(b.email) from cm_test_customers b)
  ;
%let nobs=&sqlobs;
%if &nobs %then %do;
  delete from cm_email_send a
   where lowcase(a.email) not in (select lowcase(b.email) from cm_test_customers b)
  ;
%end;
quit;

%if &nobs %then %do;
  %put Send email saying that &nobs records were transferred. ;
%end;

So let's see how it worked.

270  %if &nobs %then %do;
271    %put Send email saying that &nobs records were transferred. ;
Send email saying that 2 records were transferred.
272  %end;