What would be the proper way to convert this sql into proc sql in SAS?
FROM ALL_IMMS_POSTSPLIT AS B
WHERE A.MRNR = B.MRNR AND
A.FAMILY = B.FAMILY AND
B.IMMUNE_DATE < A.IMMUNE_DATE) + 1 AS SEQUENCED_NAME
ORDER BY A.MRNR, A.FAMILY, A.IMMUNE_DATE;
The SQL code that you present looks incomplete, and perhaps incorrect. In particular, the "&" after "a.family" and the lack of a FROM clause where the alias "A" is defined are worrisome. In general, PROC SQL can handle correlated subqueries (that's the term for the embedded SELECT) without difficulty.
Sorry, here is the corrected query. One line was missing.
A.Family & (SELECT Count(B.IMMUNE_DATE)
FROM ALL_IMMS_POSTSPLIT AS B
WHERE A.MRNR = B.MRNR And A.Family = B.Family And B.IMMUNE_DATE < A.IMMUNE_DATE) +1 AS SequencedName
FROM ALL_IMMS_POSTSPLIT AS A
ORDER BY A.MRNR, A.Family, A.IMMUNE_DATE;
This query works in Access but I'm not sure how to convert it to use in Base SAS.
In general, you should be able to copy and paste an SQL query from a database into SAS. This of course assumes the SAS program can access the tables you are referencing (i.e. SAS dataset or via an ACCESS module). With that said, I think you hit one of the few exceptions where copying charater for character just does not work. In the example posted, I think the problem is with the "&" after A.family. In Access, the "&" is used to concatenate two fields together. However, in SAS the "&" generally signals a macro variable. To resolve your issue, I would suggest adding a "||" (double pipe) in place of the "&" or you can use one of the built in concatenate functions (cat, catx, etc).
The error I get now is below. I tried to use PUT() but then it gives me a syntax error.
667 CREATE TABLE SEQUENCED AS
668 SELECT A.MRNR,
672 A.FAMILY || (SELECT COUNT(B.IMMUNE_DATE)
673 FROM ALL_IMMS_POSTSPLIT AS B
674 WHERE A.MRNR = B.MRNR
675 And A.FAMILY = B.FAMILY
676 And B.IMMUNE_DATE < A.IMMUNE_DATE) + 1 AS SequencedName
677 FROM ALL_IMMS_POSTSPLIT AS A
678 ORDER BY A.MRNR, A.FAMILY, A.IMMUNE_DATE; ERROR: Concatenation (||) requires character operands.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
I am not sure how to resolve the new issue. Looking at the code, it looks like you are adding 1 to the results of the subquery. I would try to wrap the subquery and the '+1' in parentheses; I am not sure of the order of execution here (i.e. if it concatenates the results and then tries to add 1 to the concatenated string).
Another option to try is to use the left() function. Since you are converting a number, you will want to have the number left justified. If not, you may have "blanks" between the value from A.family and the results of the subquery. Note: A trim function nested within the left function may also be necessary