BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Quartz | Level 8

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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

Shmuel
Garnet | Level 18

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

 

Chek documentation of PROC TRANSPOSE.

andreas_lds
Jade | Level 19

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;
PaigeMiller
Diamond | Level 26

@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?

--
Paige Miller
NewUsrStat
Quartz | Level 8

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!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 520 views
  • 1 like
  • 5 in conversation