topic Re: Split data into multiple columns in New SAS User

Split data into multiple columns

NewUsrStat — Mon, 12 Dec 2022 14:29:28 GMT

Hi guys,

suppose to have the following:

data DB; input ID :$20. (Start End)(:date9.) Money :$8.; format Start End date9.; cards; 0001 01JAN2015 06FEB2015 435 0001 08FEB2015 31DEC2015 87 0001 01JAN2016 31DEC2016 76 0001 01JAN2017 31DEC2017 768 0001 01JAN2018 15JUL2018 76 0001 18JUL2018 31DEC2018 234 0002 01JAN2017 12JUL2017 98 0002 12JUN2017 18JUN2017 0 ...;

I just would like the following:

ID Start End Year_2015 Year_2016 Year_2017 Year_2018

0001 01JAN2015 06FEB2015 435 0 0 0

0001 08FEB2015 31DEC2015 87 0 0 0

0001 01JAN2016 31DEC2016 0 76 0 0

0001 01JAN2017 31DEC2017 0 0 768 0

0001 01JAN2018 15JUL2018 0 0 0 76

0001 18JUL2018 31DEC2018 0 0 0 234

0002 01JAN2017 12JUL2017 0 0 98 0

0002 12JUN2017 18JUN2017 0 0 0 0

Can anyone help me please?

ID Start End Year_2015 Year_2016 Year_2017 Year_2018

0001 435 76 768 76

Re: Split data into multiple columns

PeterClemmensen — Mon, 12 Dec 2022 14:32:31 GMT

Is this sample data representable of your actual data? What if an observation spans multiple years?

Re: Split data into multiple columns

Shmuel — Mon, 12 Dec 2022 14:36:57 GMT

Can START and END be in different years? What column shall hold the value?

Chek documentation of PROC TRANSPOSE.

Re: Split data into multiple columns

andreas_lds — Tue, 13 Dec 2022 08:49:52 GMT

Why is "money" alphanumeric?

Idea: Use a data step to extract the year form "Start" or "End" into a new variable, use proc transpose (as already suggest) to create a hardly useful dataset. Code for proc transpose:

proc transpose data=have_year out=want(drop=_name_) prefix=year_;
   by Id Start End;
   id Year;
   var Money;
run;

Re: Split data into multiple columns

PaigeMiller — Tue, 13 Dec 2022 11:25:16 GMT

@NewUsrStat wrote:

Hi guys,

suppose to have the following:

data DB; input ID :$20. (Start End)(:date9.) Money :$8.; format Start End date9.; cards; 0001 01JAN2015 06FEB2015 435 0001 08FEB2015 31DEC2015 87 0001 01JAN2016 31DEC2016 76 0001 01JAN2017 31DEC2017 768 0001 01JAN2018 15JUL2018 76 0001 18JUL2018 31DEC2018 234 0002 01JAN2017 12JUL2017 98 0002 12JUN2017 18JUN2017 0 ...;

I just would like the following:

ID    Start End Year_2015 Year_2016 Year_2017   Year_2018

0001 01JAN2015 06FEB2015 435 0    0 0

0001    08FEB2015 31DEC2015 87    0 0 0

0001 01JAN2016 31DEC2016 0 76 0 0

0001 01JAN2017 31DEC2017 0 0 768 0

0001 01JAN2018 15JUL2018 0 0 0 76

0001 18JUL2018 31DEC2018 0 0 0 234

0002 01JAN2017 12JUL2017 0 0 98 0

0002 12JUN2017 18JUN2017 0 0 0 0

In most cases, this is a poor arrangement of data where you will have more difficulties programming whatever you are planning to do than by leaving the original data unchanged. Which brings up the question, what are you planning to do once you have the data re-arranged?

Re: Split data into multiple columns

NewUsrStat — Tue, 13 Dec 2022 14:47:30 GMT

Hi all, thank you for your suggestion. At the end there's a line that I cannot remove. I don't know why I cannot edit. Anyway, I know that it is not convenient to transpose the data in that way but it is something I have to do to run another code. Thank you again!