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!
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!
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
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)
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: