Look up values and do loop

[BhargavDesai]

Hello,

Happy New Year!  

I am seeking help/ideas to do following.  I use SAS Enterprise Guide 7.1.

I have two data sets  :  one containing detailed data and one with look up values

DataSet A :   ProductGroup  SaleDate       Quantity  Value

                      ProductA           12/01/2019    9            $123.00

                      ProductA           12/02/2019    134       $ 2345.00

                      PRoductB         12/01/2019     123      $  2000.00

                      ProductB            12/10/2019    45        $ 175.00

                      ProductC          ----                    -- 

                     ProductD

DataSet B     ProductGroup  Period1_startdate  period1_endDt   period2_startDt  Period2_endDt

                       ProductA           12/01/2019            12/03/2019        12/04/2019         12/10/2019

                       ProductB            12/03/2019            12/05/2019       12/06/2019         12/19/2019

                      ProductC

What I want to do is :

                  For each product I want to create two datasets -  first containing data withing period1 and second withing period2 (saleDate between period1_sartDate and Period1_EndDt)

Can someone please guide me how can this be done?   DatasetB  will be EXCEL table that will be read in.

Thanks in advance!

[novinosrin]

Hi @BhargavDesai  To help lazy people like me , Can you please post proper/complete neat samples of your 

1. Sample data

2. Expected data i.e output

Of course, I agree somebody will go above and beyond but I am just asking.  Thanks!

[ed_sas_member]

Hi @BhargavDesai 

You can try this for example. Let me know if that does correspond to what you expect.

Best,

data DataSetA;
	infile datalines truncover;
	input ProductGroup $ SaleDate:MMDDYY10. Quantity Value dollar8.2;
	format SaleDate MMDDYY10. Value dollar8.2;
	datalines;
ProductA 12/01/2019 9 $123.00
ProductA 12/02/2019 134 $2345.00
ProductB 12/01/2019 123 $2000.00
ProductB 12/10/2019 45 $175.00
ProductC   
ProductD   
;
run;

data DataSetB;
	infile datalines truncover;
	input ProductGroup $ Period1_startdate:MMDDYY10. period1_endDt:MMDDYY10. period2_startDt:MMDDYY10. Period2_endDt:MMDDYY10.;
	format Period1_startdate period1_endDt period2_startDt Period2_endDt MMDDYY10.;
	datalines;
ProductA 12/01/2019 12/03/2019 12/04/2019 12/10/2019
ProductB 12/03/2019 12/05/2019 12/06/2019 12/19/2019
ProductC    
;
run;

data DataSet_all;
	merge DataSetA DataSetB (in=x);
	by ProductGroup;
	if x=1;
run;

proc sql noprint;
	select distinct ProductGroup into: List_prodB separated by " " from DataSetB;
	select distinct ProductGroup into: product1-:product999 from DataSetB;
	select count(distinct ProductGroup) into: nb_prodB from DataSetB;
run;

%macro create_dst();
	%do i = 1 %to &nb_prodB;
		data Period1_&&Product&i Period2_&&Product&i;
			set dataset_all;
			where ProductGroup = "&&Product&i.";
			if Period1_startdate <= SaleDate <= period1_endDt then output Period1_&&Product&i;
			if period2_startDt <= SaleDate <= period2_endDt then output Period2_&&Product&i;
		run;
	%end;
%mend;

%create_dst

[ed_sas_member]

Hi @BhargavDesai 

Another way to achieve this could also be:

data DataSetA;
	infile datalines truncover;
	input ProductGroup $ SaleDate:MMDDYY10. Quantity Value dollar8.2;
	format SaleDate MMDDYY10. Value dollar8.2;
	datalines;
ProductA 12/01/2019 9 $123.00
ProductA 12/02/2019 134 $2345.00
ProductB 12/01/2019 123 $2000.00
ProductB 12/10/2019 45 $175.00
ProductC   
ProductD   
;
run;

data DataSetB;
	infile datalines truncover;
	input ProductGroup $ Period1_startdate:MMDDYY10. period1_endDt:MMDDYY10. period2_startdate:MMDDYY10. Period2_endDt:MMDDYY10.;
	format Period1_startdate period1_endDt period2_startdate Period2_endDt MMDDYY10.;
	datalines;
ProductA 12/01/2019 12/03/2019 12/04/2019 12/10/2019
ProductB 12/03/2019 12/05/2019 12/06/2019 12/19/2019
ProductC    
;
run;

data DataSet_all;
	merge DataSetA DataSetB (in=x);
	by ProductGroup;
	if x=1;
run;

%macro create_dst(j); /* parameter j = period number e.g. 1 or 2 */
	data _null_;
		declare hash h ();
		h.definekey("i");
		h.definedata("ProductGroup","SaleDate","Quantity","Value");
		h.definedone();
	
		do i=1 by 1 until (last.ProductGroup);
			set DataSet_all (where = (Period&j._startdate <= SaleDate <= period&j._endDt));
			by ProductGroup;
			h.add();
			h.output(dataset:cats("work.","period&j._",ProductGroup));
		end;
	run;
%mend;

%create_dst(1)
%create_dst(2)