topic Convert SQL query to Macro Proc SQL in SAS Programming

Convert SQL query to Macro Proc SQL

Sathish_jammy — Tue, 04 Dec 2018 07:00:37 GMT

Hi,

I newly started to work in Proc SQL,

Even though I successfully done with the below code, which have a simple condition in SET statement.

%macro myUpd2(tbn, vart, varf, from);

proc sql;
update &tbn. as b
set &vart. = (select &varf. from &from. where 
b.Mno = Patient_id and record_date = (select max(record_date) from &from. where b.Mno = Patient_ID)
)
where &vart. is null;
quit;
%mend myUpd2;

%myUpd2(Proj.flsha, aad, diab_ageonset, Mvdsc.medical1);

But I failed on the SET statement that given below to write in ProcSQL.

I need some guidance to solve the given code which using UNION statements.

update Tab1
set fvfbs = b.Test_Result_Value
FROM Tab1 a JOIN (select * from test_results union select * from test_results_archive) b 
ON a.mno = b.patient_id and fvfbs is null and
b.record_date = (select max(record_date) from (select * from test_results where param_id = 'fbs' union select * from test_results_archive where param_id = 'fbs') cc where cc.patient_id = a.mno) and
param_id = 'fbs'

Thanks in advance!

Re: Convert SQL query to Macro Proc SQL

SASKiwi — Mon, 03 Dec 2018 07:30:01 GMT

PROC SQL updates require a separate sub-query for each SET column as demonstrated in this post:

https://communities.sas.com/t5/SAS-Programming/Faster-way-to-write-a-SQL-Update-Query/td-p/166222

That's why many SAS programmers prefer other techniques like MODIFY.

Re: Convert SQL query to Macro Proc SQL

RW9 — Mon, 03 Dec 2018 08:44:38 GMT

"I newly started to work in Proc SQL, I'm good in SQL codes but not in PROC SQL." - statement does not make sense. Proc SQL implements ANSI SQL which is the basis for most SQL compilers out there, which if you know one you know the rest. Therefore you either know it or you don't.

Second off, don't jump into writing macro code. Write base SAS first, then if there is benefits to it, convert it into a macro. There are benefits to using Macro, however in most cases I see its used in trying to replace Base SAS which just results in messy unmaintable code.

Third. Learn SAS, it is the language you are using here. SQL can have a use in SAS, but learning the actual language will help you write simpler, easier to maintain, and more efficient code.