BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Teamtim
Fluorite | Level 6

I have a data in this format:

id       years sex state

100002  0       0    1

100002  1       0    1

100002  2       0    2

100002  3       0    2

100002  4       0    2

100002  5       0    3

100004  0       1   1

100004  1.2    1   1

100004  2.4    1   1 

and I want to create additional new three columns ( "start" & "end" out of "state", and "time" out of "age") such as the one below

id          years sex state start End Time

100002    0       0    1       1    1      1

100002    1       0    1       1     2     2

100002    2       0    2       2     2     3

100002    3       0    2       2     2     4

100002    4       0    2       2     3     5

100004    0       1   1        1    1   1.2

100004  1.2      1   1         1    1   2.4 

Please is there anyone who can help me out with this in SAS. Thanks I really appreciate.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

It makes me confused . what is out of "state", and what is out of "age" ?

data have;
input id       years sex state ;
cards;
100002  0       0    1
100002  1       0    1
100002  2       0    2
100002  3       0    2
100002  4       0    2
100002  5       0    3
100004  0       1   1
100004  1.2    1   1
100004  2.4    1   1 
;
run;
data one two(keep=state years rename=(state=end years=time));
 set have;
 by id;
 start=state;
 if not first.id then output two;
 if not last.id then output one;
run;
data want;
 merge one two;
run;

Xia Keshan

View solution in original post

4 REPLIES 4
AncaTilea
Pyrite | Level 9

Your initial data structure is rather confusing.

Your initial data has 6 rows for ID 100002 and 3 rows for 100004.

What is the logic that makes the last row for each ID go away in the data you want?

I can see that for the 'time' variable you are simply bringing up the 'years' variable values, but not if it's zero?

Provide a bit more detail.

Anca.

Teamtim
Fluorite | Level 6

When I re-arrange the "state" variable into "end" and "start" the first year is not not needed any more which such that when the last one is used I then have an extra row which is not needed.

AncaTilea
Pyrite | Level 9

How about this?

assuming your example data set is named have;

data want;

     set have;

     by id;

     if eof1 = 0 then set have (firstobs = 1 keep = state rename = (state = start)) end = eof1;

     else start = .;

     if eof1 = 0 then set have (firstobs = 2 keep = state years

           rename = (state = end years = time)) end = eof1;

     else end= .;

if last.id then delete;

run;

I hope it helps.

Anca.

Ksharp
Super User

It makes me confused . what is out of "state", and what is out of "age" ?

data have;
input id       years sex state ;
cards;
100002  0       0    1
100002  1       0    1
100002  2       0    2
100002  3       0    2
100002  4       0    2
100002  5       0    3
100004  0       1   1
100004  1.2    1   1
100004  2.4    1   1 
;
run;
data one two(keep=state years rename=(state=end years=time));
 set have;
 by id;
 start=state;
 if not first.id then output two;
 if not last.id then output one;
run;
data want;
 merge one two;
run;

Xia Keshan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 939 views
  • 3 likes
  • 3 in conversation