topic Look up values and do loop in SAS Programming

Look up values and do loop

BhargavDesai — Mon, 30 Dec 2019 14:58:25 GMT

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!

Re: Look up values and do loop

novinosrin — Mon, 30 Dec 2019 15:01:51 GMT

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!

Re: Look up values and do loop

ed_sas_member — Mon, 30 Dec 2019 15:23:54 GMT

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

Re: Look up values and do loop

ed_sas_member — Mon, 30 Dec 2019 16:40:46 GMT

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)