RE: Data Selection

Accepted Solution Solved
Reply
Regular Contributor
Posts: 221
Accepted Solution

RE: Data Selection

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;


Accepted Solutions
Solution
‎11-21-2015 03:53 PM
Respected Advisor
Posts: 4,606

Re: RE: Data Selection

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.

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,606

Re: RE: Data Selection

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;
PG
Regular Contributor
Posts: 221

Re: RE: Data Selection

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

Solution
‎11-21-2015 03:53 PM
Respected Advisor
Posts: 4,606

Re: RE: Data Selection

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.

PG
Regular Contributor
Posts: 221

Re: RE: Data Selection

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 290 views
  • 0 likes
  • 2 in conversation