topic Identify Purchase date in SAS Programming

Identify Purchase date and selling date based on transaction

subrat1 — Tue, 14 May 2019 03:28:25 GMT

I wants to create purchase date based on quantity being purchased on any month, similarly i also wants to create a selling date if the quantity is either sold or if not sold then the last month as selling date as shown in below table as example.

Mutual Fund	Quantity_201801	Quantity_201802	Quantity_201803	Quantity_201804	Quantity_201805	Quantity_201806	Quantity_201807	Purchase Date	Selling Date
347	0	0	40000	40000	50000	0	0	201803	201806
218	0	6000	50000	8000	9000	9000	9000	201802	201807
326	20000	20000	20000	20000	0	0	0	201801	201805
445	0	0	0	0	700	700	700	201805	201807

Identify Purchase date

subrat1 — Tue, 14 May 2019 03:21:04 GMT

I want to identify the purchasing date based on Quantity amount being present, and then take suffix of the column as date as shown below.

Here in below example i want to create a column name Purchase date where date value is reflecting the moment customer purchase any quantity.

Mutual Fund	Quantity_201801	Quantity_201802	Quantity_201803	Quantity_201804	Quantity_201805	Purchase Date
347	0	0	40000	40000	50000	201803
218	0	6000	50000	8000	9000	201802
326	20000	20000	20000	20000	20000	201801
445	0	0	0	0	700	201805

Re: Identify Purchase date and selling date based on transaction

ChrisNZ — Tue, 14 May 2019 04:25:37 GMT

You've been around for a while now @subrat1

Please provide the input data as ready-to-run code (a data step or proc sql).

Re: Identify Purchase date

Jagadishkatam — Tue, 14 May 2019 05:14:36 GMT

please try the below code and test it

data have;
input Mutual_Fund Quantity_201801 Quantity_201802 Quantity_201803 Quantity_201804 Quantity_201805;
cards;
347 0 0 40000 40000 50000
;

data want;
set have;
array vars(*) Quantity_201801 Quantity_201802 Quantity_201803 Quantity_201804 Quantity_201805;
array vars2(*)$100. var1 - var5;
do i = 1 to dim(vars);
if vars(i)>0 then vars2(i)=scan(vname(vars(i)),2,'_');
end;
product_date=input(coalescec(of var1-var5),best.);
drop var: i;
run;