BookmarkSubscribeRSS Feed
Decay2020
Fluorite | Level 6

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 2Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10Dec-10Jan-11Feb-11
AJun-10Sep-1014556567687812324435
BOct-10Dec-106525457654577678678434789
3 REPLIES 3
Kurt_Bremser
Super User

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
Super User

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
Super User

@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

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 718 views
  • 1 like
  • 4 in conversation