get the data as per different date

[Decay2020]

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

[Kurt_Bremser]

Transpose to a long format, and it's a simple WHERE condition.

Maxim 19: Long Beats Wide, aka Do Not Keep Data in Structure.

For a coding example, supply data in usable form (data step with datalines).

[ballardw]

Are those actually your variable names? "Jun-10" is a non-standard name and using such just adds coding issues.

Provide data step code that will create a working version of your example data. That way we know exactly what you are working with.

And show you expect the result to look.

[Reeza]

@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/

Spoiler

---

@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

---