RE: Data Selection

[twildone]

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;

[PGStats]

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;

[twildone]

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

[PGStats · Accepted solution]

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.

[twildone]

Thanks PG.....That worked perfectly!!!....