topic Re: Create New Variable by Getting Begining and Ending Value in SAS Programming

Create New Variable by Getting Begining and Ending Value

turcay — Thu, 24 Jan 2019 00:01:43 GMT

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

Re: Create New Variable by Getting Begining and Ending Value

LaurieF — Thu, 24 Jan 2019 00:37:16 GMT

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?

Re: Create New Variable by Getting Begining and Ending Value

r_behata — Thu, 24 Jan 2019 01:38:54 GMT

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;

Re: Create New Variable by Getting Begining and Ending Value

novinosrin — Thu, 24 Jan 2019 02:49:25 GMT

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.

Re: Create New Variable by Getting Begining and Ending Value

turcay — Thu, 24 Jan 2019 08:28:11 GMT

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

Re: Create New Variable by Getting Begining and Ending Value

Ksharp — Thu, 24 Jan 2019 08:43:12 GMT

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;

Re: Create New Variable by Getting Begining and Ending Value

turcay — Thu, 24 Jan 2019 08:56:45 GMT

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;

Re: Create New Variable by Getting Begining and Ending Value

Ksharp — Thu, 24 Jan 2019 09:07:03 GMT

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;

Re: Create New Variable by Getting Begining and Ending Value

turcay — Thu, 24 Jan 2019 09:23:11 GMT

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

Re: Create New Variable by Getting Begining and Ending Value

novinosrin — Thu, 24 Jan 2019 14:41:09 GMT

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.