BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
abdulla
Pyrite | Level 9
GVKEYFYIDJoinStart
100420001111.2000
100420011111.2000
100420021112.2002
100520001112.2000
100520011112.2000
100520021113.2002
100619951113.1995
100619961113.1995
100619971113.1995
10072005111420022002
10072006111420022002
10072007111420022002

My desired result is the last column (start). What I am trying to do is the following:

1. If join (4th column) is given then start=join

2. If "join" is not given then start=fy. However, "start" will be only first FY based on ID. For example, 

in the first row for ID 1111, the "start" is 2000 as it is the first year. 

In the 2nd row for same ID 1111, the "start" is also 2000 not 2001. 

in the 3rd row ID is changed. So, start=fy

in the fourth row ID is same but gvkey is changed. So, for being a new gvkey(another company), for ID 1112, the start=fy and so on. 

 

I don't know how to code this. Can anyone help me, please? 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20


data have;
input GVKEY	FY	ID	Join;*	Start ;
cards;
1004	2000	1111	.	2000
1004	2001	1111	.	2000
1004	2002	1112	.	2002
1005	2000	1112	.	2000
1005	2001	1112	.	2000
1005	2002	1113	.	2002
1006	1995	1113	.	1995
1006	1996	1113	.	1995
1006	1997	1113	.	1995
1007	2005	1114	2002	2002
1007	2006	1114	2002	2002
1007	2007	1114	2002	2002
;

data desired;
 set have;
 by gvkey id;
 retain start;
 if first.id and join=. then start=fy;
 if join then start=join;
run;

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20


data have;
input GVKEY	FY	ID	Join;*	Start ;
cards;
1004	2000	1111	.	2000
1004	2001	1111	.	2000
1004	2002	1112	.	2002
1005	2000	1112	.	2000
1005	2001	1112	.	2000
1005	2002	1113	.	2002
1006	1995	1113	.	1995
1006	1996	1113	.	1995
1006	1997	1113	.	1995
1007	2005	1114	2002	2002
1007	2006	1114	2002	2002
1007	2007	1114	2002	2002
;

data desired;
 set have;
 by gvkey id;
 retain start;
 if first.id and join=. then start=fy;
 if join then start=join;
run;
RichardDeVen
Barite | Level 11

The logic could also be coded as if/then/else

data desired;
  set have;
  by gvkey id;

  retain start;

  if join then 
    start = join;
  else
  if first.id then
    start = fy;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 437 views
  • 3 likes
  • 3 in conversation