Create New Variable by Getting Begining and Ending Value

[turcay]

Hello everyone,

I would like to ask a question which can be little bit tricky and I cannot figure out any solution for this.  I created a sample “HAVE” data set and this include Date variable with character type and YYMMN6. Format. This Date variable shifts in 12 months periods. I want to create new variable with begining and end value in Date variable and I also want to add Period variable sequentially.

Data Have;
Length ID 8 DateCharacter $ 32;
Infile Datalines Missover;
Input  ID DateCharacter;
Datalines;
1 201601
1 201602
1 201603
1 201604
1 201605
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
2 201601
2 201602
2 201603
2 201604
2 201605
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
;
RUN;

It sounds complicated but by examining the followin desired output, you can easily understand my purpose.

Can somebody help me to write the code to help to reach my aim, please?

Thanks

[LaurieF]

This produces what you want:

Data Have;
Length ID 8 DateCharacter $ 6
       date 4
       period 3
       table_name $ 19;
format date ddmmyy10.;
Infile Datalines Missover;
Input  ID DateCharacter;
period = inputn(substr(datecharacter, 5, 2), '2.');
date = mdy(1, 1, inputn(substr(datecharacter, 1, 4), '4.'));
table_name = putn(date, 'date9.') || '_' || putn(intnx('month', date, 11, 'b'), 'date9.');

keep id datecharacter period table_name;
Datalines;
1 201601
1 201602
1 201603
1 201604
1 201605
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
2 201601
2 201602
2 201603
2 201604
2 201605
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
;
RUN;

The only complex bit is the creation of the tablename variable.

table_name = putn(date, 'date9.') || '_' || putn(intnx('month', date, 11, 'b'), 'date9.');

The intnx function advances the date variable (which contains 1janyyyy) by 11 months to 1decyyyy - the 'b' parameter (which is the default) makes it the first day of the period (in this case month).

is that all you need?

[r_behata]

Data Have;
Length ID 8 DateCharacter $ 32;
Infile Datalines Missover;
Input  ID DateCharacter;
Datalines;
1 201601
1 201602
1 201603
1 201604
1 201605
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
2 201601
2 201602
2 201603
2 201604
2 201605
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
;
RUN;


data want;
	set have;
	
	yrmon=input(DateCharacter,yymmn6.);
	period=month(yrmon);
	
	Table_Name=cats(put(intnx('year',yrmon,0,'beginning'),date9.),'_',
		put(intnx('month',intnx('year',yrmon,0,'end'),0,'beginning'),date9.));
	drop yrmon;
run;

[novinosrin]

if you guarantee the below , 

"This Date variable shifts in 12 months periods."  and "if first record for each id is jan i.e month is 1 " 

the process can be made efficient by retaining and assingning on the condition if mod(month(date),12)=1 then want var(your concat excercise) is assigned and retained. 

[turcay]

Hello,

Actually, you mentioned about the correct point. The first record cannot be January everymonth, what if is starts with June then it should start with 01June2016_01May_2016.

So I think, I need to change the code, right?

Thanks

[Ksharp]

Data Have;
Input  ID DateCharacter $;
Datalines;
1 201601
1 201602
1 201603
1 201604
1 201605
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
2 201601
2 201602
2 201603
2 201604
2 201605
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
;
RUN;
data temp;
 set have;
 d=input(DateCharacter,yymmn6.);
 format d date9.;
run;


proc sql;
create table want as
select *,cats(put(min(d),date9.),'_',put(max(d),date9.)) as table_name length=40
 from temp
  group by id
   order by id,d;
quit;

[turcay]

Thank you very much @LaurieF , @Ksharp , @novinosrin  and @r_behata .

What if I use the following data set? How can I create Period variable?

Data Have;
Input  ID DateCharacter $;
Datalines;
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
1 201701
1 201702
1 201703
1 201704
1 201705
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
2 201701
2 201702
2 201703
2 201704
2 201705
;
RUN;

[Ksharp · Accepted solution]

If there was not gap, that would be easy.

Data Have;
Input  ID DateCharacter $;
Datalines;
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
1 201701
1 201702
1 201703
1 201704
1 201705
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
2 201701
2 201702
2 201703
2 201704
2 201705
;
RUN;
data have;
 set have;
 by id;
 if first.id then period=0;
 period+1;
run;

[turcay]

Like following; 

data temp;
 set have;
 by id;
 if first.id then period=0;
 period+1;
  d=input(DateCharacter,yymmn6.);
 format d date9.;
run;

proc sql;
create table want as
select *,cats(put(min(d),date9.),'_',put(max(d),date9.)) as table_name length=40
 from temp
  group by id
   order by id,d;
quit;

It seems it is working without any problem

[novinosrin]

I really do like @Ksharp  solution that uses the min and max which is safe and true.  Anyways, he is one SAS genie I tend to blindly trust regardless.