sas sql应用技巧

COALESCE function
In our administrative tables, the Social Security Number (SSN) field is not very well populated. We need to go after different sources: membership tables (old, current, and daily) and other utilization tables. PROC SQL makes the selection process very easy, where the COALESCE function will pick the first non-missing value.
PROC SQL; CREATE TABLE _SSNINFO AS
SELECT S.*, COALESCE (C.SSN, A.SSN, B.SSN, D.SSN) AS SSN,
FROM _SAMPLE AS S LEFT JOIN _CMS AS A ON S.HRN=A.HRN
LEFT JOIN _MG AS B ON S.HRN=B.HRN
LEFT JOIN _CMSDL AS C ON S.HRN=C.HRN
LEFT JOIN _DOCPLUS AS D ON S.HRN=D.HRN
;QUIT;

Access other databases
Below is SQL code to query an Oracle table from PC SAS. (OO.CMS_MEMBER is our membership file with 400,000+ rows). PROC SQL is the only way you can join a SAS table and an Oracle table. Method 1 uses join while Method 2 uses subquery. As we can see from the chart on the next page Method 2 is super fast for a small sample.
D 1--;
*--METHOPROC SQL;
CREATE TABLE TTT AS
SELECT B.HRN, FAMACT, RELTN
FROM XSAMPLE AS A INNER JOIN OO.CMS_MEMBER (dbkey=hrn dbindex=yes) AS B
ON A.HRN =B.HRN
;QUIT;
*--METHOD 2--;
PROC SQL NOPRINT; SELECT HRN INTO :HRN_LIST SEPARATED BY ',' FROM xsample; QUIT;
PROC SQL;
CREATE TABLE TTT AS
SELECT HRN, FAMACT, RELTN
FROM OO.CMS_MEMBER (dbkey=hrn dbindex=yes)
WHERE HRN IN (&HRN_LIST)
;QUIT;
Note: The maximum for &HRN_LIST under Windows SAS version 8, for 8 digits HRN, is about 1000 HRNs; in other words, the SQL limit comes before SAS

Textwrapping
When you have a long character variable (such as a COMMENT field in the questionnaire), and you want to print the values using PROC PRINT, you will get the warning message:
WARNING: Data too long for column "COMMENT"; truncated to 124 characters to fit.
A simple solution is to use PROC SQL with the flow option. An alternative would be to use PROC REPORT. (Note: Flow=30 has an effect on all character variables.)
PROC SQL FLOW=30;
SELECT HRN, COMMENT
FROM A
;QUIT;
HRN COMMENT
--------------------------------------
12345678 LONGTEXTTTTTTTTTTTTTTTTTTTTT
TTTTTTTTTTTTTTTTTTTTTTTTTTTT
TTTTTTTTTTTTTTTTTTTTTTTTTTTT
TTTTTTTTTTTTTTTTTTTTTTTTTTTT
87654321 LONGTEXTTTTTTTTTTTTTTTTTTTTT
TTTTTTTTTTTTTTTTTTTTTTTTTTTT
TTTTTTTTTTTTTTTTTTTTTTTTTTTT
TTTTTTTTTTTTTTTTTTTTTTTTTTTT