Proc SQL: Using Joins in an Update Statement

I ran into simple use case the other day -- with a surprising outcome. There was a parent and a child table, and I wanted to use an UPDATE query in PROC SQL to update one field of all parent records linked to a child record with a certain value.

In MS Access, the following worked just fine:

UPDATE tblParent INNER JOIN tblChild ON tblParent.pID = tblChild.pID
SET tblParent.field = "updated" WHERE (((tblChild.yesno)=1)) ;

The same statement did not work in SAS. Apparently, PROC SQL does not allow joins in the beginning of an UPDATE statement:
9    PROC SQL;
10   UPDATE tblParent JOIN tblChild ON tblParent.pID = tblChild.pID
                      ----
                      22
                      76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, SET.
ERROR 76-322: Syntax error, statement will be ignored.
11   SET tblParent.field = "updated" WHERE (((tblChild.yesno)=1));
12   QUIT;

In the end, I had to use a subquery. It was probably less efficient than a join, but my table wasn't large enough for it to be a big deal. The solution:
PROC SQL;
   UPDATE tblParent A
   SET field = (SELECT DISTINCT "updated" FROM tblChild B WHERE B.pID = A.pID AND B.yesno=1);
QUIT;

If anyone has a better solution to this or some sort of workaround, please post a comment below! I tried searching Google for a better solution but did not find one.

Two Methods to Create a CSV: Proc Export and the Data Step

Sample PROC Export procedure to export a SAS dataset to CSV:

proc export data=work.DATASET_NAME;
     outfile='c:\users\public\EXPORTED_FILE_NAME.csv'
     replace
     dbms=dlm;
     delimiter=',';
run;

Sample DATA Step to export a SAS dataset to CSV:

data _null_;
     set work.DATASET_NAME;
     file 'c:\users\public\EXPORTED_FILE_NAME.csv' dlm=',';
     put var1 var2 var3;
run;

The above two statements are essentially equivalent, with two exceptions: the data step does not output field names to the CSV file, and the data step requires that desired variables be explicitly included in the PUT statement.

SAS Macro to Print Record Counts for All Tables in a Library

/*----- PRINT RECORD COUNTS OF ALL TABLES IN LIBRARY -----*/
/* Looping code from Paper 93-26 by Edward Moore:
http://www2.sas.com/proceedings/sugi26/p093-26.pdf
Many thanks to the author. */
%MACRO countRecs(lib, tempListTable, tempOutputTable);
/*----- Make list of all tables in library -----*/
PROC SQL noprint;
CREATE TABLE &tempListTable AS
SELECT DISTINCT memname
FROM dictionary.tables
WHERE UPCASE(LIBNAME) = UPCASE("&lib");
QUIT;


/*----- Scan through &tempListTable -----*/
DATA _NULL_;
IF 0 THEN SET &tempListTable NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;


/*----- Make table to hold record counts -----*/
DATA &tempOutputTable;
LENGTH tbl $29. ct 8;
RUN;


/*----- Loop -----*/
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */
DATA _NULL_;
SET &tempListTable (FIRSTOBS=&I);
CALL SYMPUT('tbl',memname);
STOP;
RUN;
/* Populate datasets */
PROC SQL noprint;
INSERT INTO &tempOutputTable (tbl, ct) SELECT "&tbl" as tbl, COUNT(*) as ct FROM &lib..&tbl;
QUIT;
%END;


/*----- Print Result -----*/
PROC PRINT DATA = &tempOutputTable;
RUN;


/*----- Delete Temporary Tables -----*/
PROC DATASETS LIBRARY=work noprint;
DELETE &tempListTable &tempOutputTable;
QUIT;
%MEND countRecs;


%countRecs(WORK, kljgKGJ3208tjg, asvbjik4o9ksjd9);

SAS Macro to Import CSV Files into SAS

/***********************************************************
IMPORT CSV files into SAS Work Library
June 10, 2011
http://sastipsbyhal.blogspot.com/
*/
%LET file1 = myFirstFile.csv;
%LET file2 = mySecondFile.csv;
%LET file3 = myThirdFile.csv;
%LET file4 = myFourthFile.csv;
%LET file5 = myFifthFile.csv;
%LET file6 = mySixthFile.csv;
%LET file7 = mySeventhFile.csv;
%LET file8 = myEighthFile.csv;
%LET fileName1 = sasNameFor_myFirstFile;
%LET fileName2 = sasNameFor_mySecondFile;
%LET fileName3 = sasNameFor_myThirdFile;
%LET fileName4 = sasNameFor_myFourthFile;
%LET fileName5 = sasNameFor_myFifthFile;
%LET fileName6 = sasNameFor_MySixthFile;
%LET fileName7 = sasNameFor_mySeventhFile;
%LET fileName8 = sasNameFor_myEighthFile;
%LET fileLoc = \\folder\where\csv\files\are\located\;
%LET fileMax = 8;
%MACRO csvIMPORT;
%Do i = 1 %to &fileMax. ;
PROC IMPORT OUT=WORK.&&fileName&i 
            DATAFILE= "&fileLoc&&file&i"
            DBMS=CSV REPLACE;
    GETNAMES=YES;
    DATAROW=2; 
RUN;
%END;
%MEND;
%csvIMPORT;

Need help choosing data visualization colors? Try ColorBrewer2.org

Are you making a data visualization and interested in adding a custom set of colors to make the information really fly off of the page?

If so, I recommend checking out ColorBrewer2.org because it has all sorts of great tools for choosing colors, including color calculators.  It also has tips on choosing color combinations that are color-blind friendly, print friendly, and more.