Proc transpose

[SASAna]

Hi SAS Users,

I need help in building the below logic.

Ex:1) If I have start_date  as 01-01-2018  and end_date as 12-31-2021   for member '123' how do I transpose them into 8 columns like below.

Ex:2) if I have start date as 01-01-208 and end_date as  12-31-2019 for member '234' then rest of the dates should be blank.

first_start_date         first_end_date  second_start_date         second_end_date  third_start_date         third_end_date  fourth_start_date         fourth_end_date

01-01-2018              12-31-2018        01-01-2019                    12-31-2019             01-01-2020              12-31-2020           01-01-2021                  12-31-2021

01-01-2018              12-31-2018        01-01-2019                    12-31-2019  

Thanks,

Ana

Thanks,

Ana

[Reeza]

So break them into start/end of years? What happens if one of the dates is in the middle of the year? Please expand your data example.
I assume you're trying to do a yearly count/calculation?
I suspect this is a step in something you're trying to do, but really isn't necessary so it may help to clarify what you're trying to do overall.

[SASAna]

Hi Reeza,

Yes, i am trying to split the data by years by calculating the year_span. I have added 3 examples of 3 diff members.

year_span = 1 + intck('YEAR',start_date, end_date,'D') ;

Ex:1) If I have start_date  as 01-01-2018  and end_date as 12-31-2021   for member '123' how do I transpose them into 8 columns like below.

Ex:2) if I have start date as 01-01-2018 and end_date as  12-31-2019 for member '234' then rest of the dates should be blank.

Ex:3) if I have start date as 01-01-2019 and end_date as  12-31-2021 for member '345'  

first_start_date         first_end_date  second_start_date         second_end_date  third_start_date         third_end_date  fourth_start_date         fourth_end_date

01-01-2018              12-31-2018        01-01-2019                    12-31-2019             01-01-2020              12-31-2020           01-01-2021                  12-31-2021

01-01-2018              12-31-2018        01-01-2019                    12-31-2019 

01-01-2019              12-31-2019        01-01-2020                    12-31-2020             01-01-2021                  12-31-2021

Thanks,

Ana

[Reeza]

Sorry, no time to fully code this but you can use INTCK() to get the number of years intervals and INTNX() to increment your dates.

Assuming you start off with something like:

ID Start End

Then your code would be something like:

data want;
set have;

nYears = intck('year', start, end);
do i=1 to nYears;
*may need some tweaking;
start_date = intnx('year', startDate, i-1, 's');
end_date = intnx('year', startdate, i-1, 'e');
output;
end;
run;

proc transpose data=want out=wide prefix=Start_Date;;
by ID:
id i;
var start_date;
run;

Repeat for End Date and then MERGE the results by ID.
This will be fully dynamic and you don't need to know how many variables you'll need ahead of time.

[PaigeMiller · Accepted solution]

This is pretty simple if you are willing to keep the data set long, instead of creating a wide data set. Furthermore, the long data set would be recommended (Maxim 19) for most additional tasks, making the subsequent programming much easier.

data have;
     id=123;
     start_date='01JAN2018'd;
     end_date='31DEC2021'd;
     output;
     id=234;
     start_date='01JAN2018'd;
     end_date='31DEC2019'd;
    output;
run;
data want;
    set have;
    start=start_date;
    instance=1;
    do while(start<=end_date);
        end=intnx('year',start,0,'e');
        output;
        instance=instance+1;
        start=intnx('year',start,1,'b');
    end;
    format start end date9.;
    drop start_date end_date;
run;

[SASAna]

Thank you. it worked very well

[Kurt_Bremser]

Split the time into observations, not columns, and let PROC REPORT do the rest.