@Kurt_Bremser is correct, transpose and then use a WHERE statement.
You could also use ARRAYs to some degree. VNAME will return the name of an array but you'll also need to use INPUT to convert it to a date for comparison. If you know that your variables are always in exact order you could use indexes but its an assumption and if it's off your values will be wrong, flipping to long doesn't have that issue.
Assumes date1, date2 are SAS dates (numeric with a date format)
Assumes months are always in order with no missing months
Assumes boundaries are included in intervals
Untested so you'll need to test this but should get you started.
data have;
infile cards dsd;
informat date1 monyy7. date2 monyy7.;
input customer $ date1 date2 'Jun-10'n 'Jul-10'n 'Aug-10'n 'Sep-10'n 'Oct-10'n 'Nov-10'n 'Dec-10'n 'Jan-11'n 'Feb-11'n;
cards;
A, Jun-10, Sep-10, 1, 45, 56, 56, 768, 78, 123, 24, 435
B, Oct-10, Dec-10, 652, 54, 576, 545, 776, 786, 78, 434, 789
;;;;
run;
proc print data=have;
format date1 date2 date9.;
run;
data want_wide1;
set have;
*declare list of months as an array;
array _dates(*) 'Jun-10'n -- 'Feb-11'n;
*convert first month to a SAS date for calculations;
first_month = input(vname(_dates(1)), monyy7.);
format first_month date1 date2 date9.;
*get index of start month - ie June 2010 is 1, July 2010 is 2;
start_index = intck('month', first_month, date1, 'C');
*get index of end month - ie June 2010 is 1, July 2010 is 2;
end_index = intck('month', first_month, date2, 'C') +1 ;
do i=1 to dim(_dates);
*if not in index range, set to 0;
if not (start_index <= i <= end_index) then _dates(i) = 0;
end;
run;
Transpose version:
proc transpose data=have out=long;
by customer date1 date2;
run;
data want_long;
set long;
if not (date1 <= input(_name_, monyy7.) <= date2) then col1=0 ;
run;
proc transpose data=want_long out=want_wide2;
by customer date1 date2;
id _name_;
idlabel _name_;
run;
Transposing data tutorials: Long to Wide: https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/ https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/ Wide to Long: https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/ https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/ Here's a tutorial on using Arrays in SAS https://stats.idre.ucla.edu/sas/seminars/sas-arrays/
@Decay2020 wrote:
Hi.. i have below data. i want to get only that data which is lesser than date given in 'date2' column.
In other words, for customer A, i want the data till sep-10 and for customer B, i want the data till dec-10 and rest should be '0'.
Thanks.
customer
date 1
date 2
Jun-10
Jul-10
Aug-10
Sep-10
Oct-10
Nov-10
Dec-10
Jan-11
Feb-11
A
Jun-10
Sep-10
1
45
56
56
768
78
123
24
435
B
Oct-10
Dec-10
652
54
576
545
776
786
78
434
789
... View more