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

turcay — Tue, 11 Jun 2019 18:43:58 GMT

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-Value/m-p/529693#M144768

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

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

heffo — Mon, 17 Jun 2019 03:39:08 GMT

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. 🙂

topic Create New Variable by Getting Begining and Ending Value - Need Short Fix in SAS Programming

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

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