Create New Variable by Getting Begining and Ending Value - Need Short Fix

[turcay]

Helle everybody,

Thanks to @Ksharp, @LaurieF , @r_behata, @novinosrin  I made it  my following question -> https://communities.sas.com/t5/SAS-Programming/Create-New-Variable-by-Getting-Begining-and-Ending-Va...

However, I want to do little changes in my question;

let's say that I have a following population ;

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 201605
2 201606
2 201607
2 201608
2 201609
2 201610
2 201611
2 201612
;
RUN;

Instead of following view;

I want to see whole periods and table_names same ->

As I tried to say as above; if 01MAY2016 is equal to period 5 in 12 months range then every 01may2016 should equal to period 5 same for other months. And table_name variable should be constant, as you can see in the image.

We need to count distinct YearMonth values then give the range between Begining and Ending Value and then assign the period values for this range. I hope I could make myself clear?

If can somebody help about this, I would be so happy.

Thanks

[heffo]

Maybe this:

*Get the first and last date for each group;
proc sql noprint;
	create table have_2 as 
	select *, min(DateCharacter) as period_start, max(DateCharacter) as period_end 
	from have
	group by ID
	order by ID, DateCharacter;
quit;


data want;
	set have_2;
	retain period;
	by id;

	*Fix the string dates to be SAS dates.;
	_d=cats(DateCharacter,"01");
	_d_start=input(cats(period_start,"01"),yymmdd10.);
	_d_end=input(cats(period_end,"01"),yymmdd10.);
	d=input(_d,yymmdd10.);

	*Count the number of periods for this ID.;
	if first.id then period = 1;
	else period = period + 1;

	*Fix the table name by concatination.;
	table_name = cats(put(_d_start,date9.),"_",put(_d_end,date9.));
	format d yymmdd10.;
	*Get rid of temporary variables;
	drop _d: period_start period_end; 
run;

BTW, it is a bit confusing that you have to look at two messages to try to figure out what you want. Better to rewrite it to one instead. 🙂