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
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;
Is this sample data representable of your actual data? What if an observation spans multiple years?
Can START and END be in different years? What column shall hold the value?
Chek documentation of PROC TRANSPOSE.
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;
@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?
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.