topic Re: Convert to proc sql in SAS in SAS Programming

Convert to proc sql in SAS

deleted_user — Wed, 06 Oct 2010 18:47:49 GMT

What would be the proper way to convert this sql into proc sql in SAS?

SELECT A.MRNR,
A.IMMUNE_DATE,
A.BIRTHDATE,
A.SEX,
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 SEQUENCED_NAME
ORDER BY A.MRNR, A.FAMILY, A.IMMUNE_DATE;

Thanks Message was edited by: egiap14

Re: Convert to proc sql in SAS

Doc_Duke — Wed, 06 Oct 2010 21:21:34 GMT

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.

Re: Convert to proc sql in SAS

deleted_user — Wed, 06 Oct 2010 23:19:20 GMT

Sorry, here is the corrected query. One line was missing.

SELECT A.MRNR,
A.IMMUNE_DATE,
A.BIRTHDATE,
A.SEX,
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.

Thanks,
Paige

Re: Convert to proc sql in SAS

Rambo — Mon, 11 Oct 2010 04:51:33 GMT

Hi Paige,

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).

I hope this helps.

Re: Convert to proc sql in SAS

deleted_user — Mon, 08 Nov 2010 16:54:43 GMT

The error I get now is below. I tried to use PUT() but then it gives me a syntax error.

PROC SQL;
667 CREATE TABLE SEQUENCED AS
668 SELECT A.MRNR,
669 A.IMMUNE_DATE,
670 A.BIRTHDATE,
671 A.SEX,
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.
679 QUIT;
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

680 RUN;

Thanks

Re: Convert to proc sql in SAS

Rambo — Tue, 16 Nov 2010 23:24:19 GMT

Hi Paige,

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

ex. left(trim(###)).

Re: Convert to proc sql in SAS

sbb — Wed, 17 Nov 2010 00:48:31 GMT

You will need to consider using the PUT(,) function to convert the number to a suitable "formatted value" character-string.

Scott Barry
SBBWorks, Inc.