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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.