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 |
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).
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.
@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.
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.