topic Re: SQL Joins and concatenation in SAS Programming

SQL Joins and concatenation

Ranjeeta — Mon, 11 Feb 2019 18:48:51 GMT

DATA CVA1819Q2;
SET CVA.FY1819Q2_VOLUME
CVA.FY1819Q2_MC (IN=MC);

/* ADD LEADING 0 FOR PROCEDURES 1-9 IN ORDER TO SORT PROPERLY */
IF SUBSTR(PROCEDURE,2,1)='.' THEN PROCEDURE = '0'||PROCEDURE;

IF MC=1 THEN PROCEDURE='11. Mitral Clip';

RUN;

Can someone advise if the set statement is concatenating the 2 data sets ? also what is placing the in=mc marker doing in the set statement?

For the proc sql code below: what is outer union corr doing ?

PROC SQL;
CREATE VIEW CVA_MS_LHIN_VIEW AS
SELECT DISTINCT
WAITLISTENTRYID,
PROCEDURE,
PATIENTLHINNAME,
'LHIN' AS SORTORDER
FROM CVA1819Q2(WHERE=(MISSING(PATIENTLHINNAME)=0))
OUTER UNION CORR
SELECT DISTINCT
WAITLISTENTRYID,
PROCEDURE,
'Ontario' AS PATIENTLHINNAME,
'PROV' AS SORTORDER
FROM CVA1819Q2(WHERE=(MISSING(PATIENTLHINNAME)=0));
QUIT;

Re: SQL Joins and concatenation

Reeza — Mon, 11 Feb 2019 19:28:14 GMT

SET concatenates or appends data set. IN=MC allows you to identify which records come from which data set in case you want to exercise conditional logic. You can see it used later, to set the procedure name for that file.

I'll leave the SQL explanation for someone else.

Re: SQL Joins and concatenation

snoopy369 — Mon, 11 Feb 2019 20:57:56 GMT

OUTER UNION simply concatenates query results (similar to how the SET works in the data step). CORR tells SQL to match up column names (without CORR, it matches up columns one-by-one without looking at the names).

This is answered in detail here: https://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n0vo2lglyrnexwn14emi8m0jqvrj.htm&docsetVersion=9.4&locale=en