topic Re: Proc transpose in SAS Programming

Proc transpose

SASAna — Tue, 29 Jun 2021 15:16:33 GMT

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

Re: Proc transpose

Reeza — Tue, 29 Jun 2021 15:29:53 GMT

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.

Re: Proc transpose

SASAna — Tue, 29 Jun 2021 15:39:35 GMT

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

Re: Proc transpose

Reeza — Tue, 29 Jun 2021 16:00:27 GMT

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.

Re: Proc transpose

PaigeMiller — Tue, 29 Jun 2021 15:52:22 GMT

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;

Re: Proc transpose

Kurt_Bremser — Tue, 29 Jun 2021 15:57:05 GMT

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

Re: Proc transpose

SASAna — Tue, 29 Jun 2021 16:37:28 GMT

Thank you. it worked very well