topic Re: Pulling data from 3 different data sets with 2 conditions in SAS Programming

Pulling data from 3 different data sets with 2 conditions

Rixile106 — Thu, 16 Nov 2023 20:16:26 GMT

Good day expects.

i have 3 data sets
MVP1 Contains clients from MVP1
MVP2 Contains clients from MVP2
Final_pdg2 contains take_up_date date of a product

how can i structure my code to produce the expected results as illustrated below

PROC SQL;
CREATE TABLE FINAL_OD_LIMIT_1 (ENCODING=ANY) AS
SELECT A.BP_ID, B.FE_GEN_CUST_NO, B.TAKE_UP_DATE_WA
FROM MVP1 A
LEFT JOIN LMT.Final_pdg2 B ON A.BP_ID = B.FE_GEN_CUST_NO
WHERE B.TAKE_UP_DATE_WA BETWEEN 20230510 AND &YDAY.
AND  (
   SELECT C.BP_ID
   FROM FIN.MVP2 C
   LEFT JOIN Final_pdg2 D ON C.BP_ID = D.FE_GEN_CUST_NO
   WHERE D.TAKE_UP_DATE_WA BETWEEN 20230828 AND &YDAY.
);
QUIT;

Re: Pulling data from 3 different data sets with 2 conditions

Patrick — Thu, 16 Nov 2023 21:53:27 GMT

To stack data you need a UNION and not a JOIN. Just based on the screenshot of your Have and Want data below code would do.

proc sql;
  create table final_od_limit_1 (encoding=any) as

  select bp_id, fe_gen_cust_no, take_up_date_wa
  from mvp1

  union corr all
  select bp_id, fe_gen_cust_no, take_up_date_wa
  from lmt.final_pdg2
  ;
quit;

You're also talking about a 3rd table which also gets used in your code - but this table is missing in the screenshot. If above code doesn't provide you sufficient guidance how to proceed then please share some representative sample data for all tables via SAS datastep code that creates this sample data.

Re: Pulling data from 3 different data sets with 2 conditions

Rixile106 — Fri, 17 Nov 2023 07:11:39 GMT

Table 1 consist of

MVP1
	BP_ID	Buckets
	134	Mvp1
	587	Mvp1
	520	Mvp1

Table 2 consist of

MVP2
	BP_ID	Buckets
	101	Mvp2
	111	Mvp2
	202	Mvp2

Table 3 consist of

FE_GEN_CUST_NO	TAKE_UP_DATE_WA
134	20230510
587	20230511
520	20230512
101	20230828
111	20230829
202	20230830
115	20230829
2088	20230830
134	20230410
587	20230411
520	20230412
101	20230415
111	2023020
202	20230211
115	20230419
2088	20230430

Expected Results	BP_ID	FE_GEN_CUST_NO	TAKE_UP_DATE_WA
	134	134	20230510
	587	587	20230511
	520	520	20230512
	101	101	20230828
	111	111	20230829
	202	202	20230830

From table 1 join table 3 and take_up_date _wa (date) is between 20230510 to date

	PROC SQL;
		CREATE TABLE WRK.FINAL_OD_LIMIT_1 (ENCODING= ANY) AS 
			SELECT 			BP_ID, FE_GEN_CUST_NO, TAKE_UP_DATE_WA
			      FROM FIN.MVPS A 
				     LEFT JOIN LMT.Final_pdg2  B ON A.BP_ID = B.FE_GEN_CUST_NO
					 	WHERE TAKE_UP_DATE_WA BETWEEN 20230510 AND &YDAY.

								UNION  ALL
								SELECT BP_ID, FE_GEN_CUST_NO, TAKE_UP_DATE_WA 
								FROM FIN.TO_MVP2_CLIENT C ON C.BP_ID = B.FE_GEN_CUST_NO 
									WHERE TAKE_UP_DATE_WA BETWEEN 20230828 AND &YDAY. ;
								;
									QUIT;

and
from table 2 join table 3 and the take_up_date_wa (date) is between 20230828 to date

Re: Pulling data from 3 different data sets with 2 conditions

Patrick — Sat, 18 Nov 2023 11:45:06 GMT

Please provide sample data via working SAS datasteps that create such data. Not only does this allow us to spend the time to actually answer your question, it also removes a lot of ambiguity how your data really looks like - for example if variable TAKE_UP_DATE_WA just contains a number or an actual SAS Date value. Please amend below data steps if it doesn't create the data as you've actually got it.

data work.table_1;
  infile datalines truncover;
  input BP_ID Buckets $;
  datalines;
134 Mvp1
587 Mvp1
520 Mvp1
;

data work.table_2;
  infile datalines truncover;
  input BP_ID Buckets $;
  datalines;
101 Mvp2
111 Mvp2
202 Mvp2
;

data work.table_3;
  infile datalines truncover;
  input FE_GEN_CUST_NO TAKE_UP_DATE_WA :yymmdd8.;
  format TAKE_UP_DATE_WA yymmddn8.;
  datalines;
134 20230510
587 20230511
520 20230512
101 20230828
111 20230829
202 20230830
115 20230829
2088 20230830
134 20230410
587 20230411
520 20230412
101 20230415
111 20230201
202 20230211
115 20230419
2088 20230430
;

In your code what's the value in macro variable &YDAY.

And looking at your selection

WHERE TAKE_UP_DATE_WA BETWEEN 20230828 AND &YDAY.

Why are there dates prior to 20230828 in your desired data?

If for a FE_GEN_CUST_NO value more than one row would be within the date range would you want to select both rows? ...or only the most recent date ...or some other selection criteria?

The following code shouldn't require much change once you clarify the required logic.

proc sql;
  select 
      l.FE_GEN_CUST_NO
    , r.BP_ID
    , l.TAKE_UP_DATE_WA
  from 
    work.table_3 l
    inner join 
    (
      select BP_ID 
      from work.table_1
      union all
      select BP_ID 
      from work.table_1
    ) r
  on 
    l.FE_GEN_CUST_NO=r.BP_ID
    and l.TAKE_UP_DATE_WA between &start_dt and &stop_dt
  ;
quit;

In case the same BP_ID could exist in both table_1 and table_2 then you would need to remove the ALL keyword from the UNION so that the data gets deduped.