BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
Doc_Duke
Rhodochrosite | Level 12
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.
deleted_user
Not applicable
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
Rambo
Calcite | Level 5
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.
deleted_user
Not applicable
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
Rambo
Calcite | Level 5
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(###)).
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
You will need to consider using the PUT(,) function to convert the number to a suitable "formatted value" character-string.

Scott Barry
SBBWorks, Inc.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 898 views
  • 0 likes
  • 4 in conversation