Hi....I am trying to select data records from a dataset based on the ID and this ID is selected under 2 conditions. Those 2 conditions are if the date is before 20150908, then I only want to select those ID's where the Surcharge is less than 10% of the Cost. Similiarly, for the second condition, if the date is after 20150908, then I only want to select those ID's where the Surcharge is less than %% of the Cost. My question is can I combine these 2 conditions under the same PROC SQL, like have below or should or need to run to PROC SQL and combine the results together after?.....Thanks in advance.
PROC SQL NOPRINT;
CREATE TABLE Want AS
SELECT A.*
FROM WORK.Summary A
LEFT JOIN (SELECT DISTINCT ID
FROM WORK.Summary
(WHERE NUM=’P585’
AND SIN=’02234566’
AND DATE < ‘20150908’
HAVING SURCHARGE < 0.1*COST
ORDER BY ID)
AND
(WHERE NUM=’P585’
AND SIN=’02234566’
AND DATE >= ‘20150908’
HAVING SURCHARGE < 0.05*COST
ORDER BY ID)) B
ON A.ID=B.ID;
QUIT;
OK, then what you need is something like
PROC SQL;
CREATE TABLE Want AS
SELECT *
FROM Summary
WHERE ID IN
( SELECT ID
FROM Summary
WHERE
NUM=’P585’ AND
SIN=’02234566’ AND
( DATE < '08SEP2015'd AND SURCHARGE < 0.10*COST OR
DATE >= '08SEP2015'd AND SURCHARGE < 0.05*COST ) );
QUIT;
or the equivalent if dates are character strings.
Although not syntaxically correct, your SQL query seems to be equivalent to
PROC SQL;
CREATE TABLE Want AS
SELECT *
FROM Summary
WHERE
NUM=’P585’ AND
SIN=’02234566’ AND
( DATE < '08SEP2015'd AND SURCHARGE < 0.10*COST OR
DATE >= '08SEP2015'd AND SURCHARGE < 0.05*COST );
QUIT;
that is, assuming your dates are real dates and not character strings. If your dates are indeed character strings, you could get away with
PROC SQL;
CREATE TABLE Want AS
SELECT *
FROM Summary
WHERE
NUM=’P585’ AND
SIN=’02234566’ AND
( DATE < '20150908' AND SURCHARGE < 0.10*COST OR
DATE >= '20150908' AND SURCHARGE < 0.05*COST );
QUIT;
Hi PG....thank you for your response. I am not too sure if I would get the complete data from your suggestion. The reason I have to select distinct ID's first is that I only want data based on when the Surcharge is less that a certain percent of the cost which changed on a certain date (20150908) for this particular Num and SIN. Once I have a list of distinct ID's based on these condition, I need to go back to the same dataset and extract a complete dataset for only these ID's as they will have other records with different SIN. So really I want to end up with a complete dataset for all the records in the dataset for only these distinct ID's if the 2 conditions are met. Hopefully this clarifies things....Thanks
OK, then what you need is something like
PROC SQL;
CREATE TABLE Want AS
SELECT *
FROM Summary
WHERE ID IN
( SELECT ID
FROM Summary
WHERE
NUM=’P585’ AND
SIN=’02234566’ AND
( DATE < '08SEP2015'd AND SURCHARGE < 0.10*COST OR
DATE >= '08SEP2015'd AND SURCHARGE < 0.05*COST ) );
QUIT;
or the equivalent if dates are character strings.
Thanks PG.....That worked perfectly!!!....
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: