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 |
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;
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 |
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;
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).
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.