topic Re: How to Drop Earliest Date and Add the Latest Date into Data Set? in SAS Programming

How to Drop Earliest Date and Add the Latest Date into Data Set?

turcay — Tue, 29 Jan 2019 23:51:53 GMT

Hello everyone,

I would like to ask you question related to data manipulation. I want to drop the noncurent year and add the current year into my data set (HAVE). Also I want to do this when my data set (HAVE) includes 12 different yearmonth value.

Actually, I made my desired output as below(data set want) but I am not sure whether it is the correct way to do this.

ıf somebody has more practical method, does s/he help me, please?

Data Have;
Input  ID YEARMONTH $;
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
;
Run;
Data Have2;
Input  ID YEARMONTH $;
Datalines;
1 201706
;
Run;

PROC SQL NOPRINT;
    SELECT COUNT(DISTINCT(YEARMONTH)) INTO :COUNTYEAR FROM Have;
QUIT;
%PUT &COUNTYEAR;

%MACRO LOOP;
%IF &COUNTYEAR=12 %THEN %DO;
PROC SQL;
CREATE TABLE HAVE AS
SELECT * FROM HAVE
HAVING YEARMONTH NE MIN(YEARMONTH);
QUIT;
PROC SQL;
CREATE TABLE WANT AS 
SELECT * FROM HAVE
 OUTER UNION CORR 
SELECT * FROM HAVE2
;
Quit;
%END;
%MEND LOOP;
%LOOP;

Thank you,

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

ChrisNZ — Wed, 30 Jan 2019 00:47:11 GMT

That's one way:

proc sql noprint;
  select count(distinct(YEARMONTH)), min(YEARMONTH) into :countyear,:minyear from HAVE;
quit;

data WANT;
  if &countyear=12 then set HAVE(where=(YEARMONTH ne "&minyear")) HAVE2;
  else set HAVE HAVE2;
run;

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

turcay — Tue, 11 Jun 2019 16:02:44 GMT

Hello,

Thank you for your response. However, what if my have2 table come 201705 again, I do not want to multiplixity. I want to prevent it. I think we need to add some more controls, right?

Data Have;
Input  ID YEARMONTH $;
Datalines;
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
1 201701
1 201702
1 201703
1 201705
;
Run;
Data Have2;
Input  ID YEARMONTH $;
Datalines;
1 201705
;
Run;

proc sql noprint;
  select count(distinct(YEARMONTH)), min(YEARMONTH),max(YEARMONTH) into :countyear,:minyear, :maxyear from HAVE;
quit;
%PUT &countyear.;
%PUT &minyear.;
%PUT &maxyear.;


data WANT;
  if &countyear=12 then set HAVE(where=(YEARMONTH ne "&minyear")) HAVE2;
  else set HAVE ;
run;

@ChrisNZ

Thanks

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

PaigeMiller — Tue, 11 Jun 2019 16:42:30 GMT

There's rarely a need to use macros or macro variables to do simple data manipulation like this.

From what I can see, HAVE has 11 rows , and WANT has 12 rows, where one more month in the sequence is added. So, this code gets you there. (Or perhaps I'm not understanding the problem entirely...)

You also make your life much simpler by handling calendar values as actual SAS date values rather than a text string.

data want;
    set have end=eof;
	ym=input(yearmonth,yymmn6.);
    output;
    if eof then do;
		ym=intnx('month',ym,1,'b');
		output;
	end;
	format ym yymm6.;
run;

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

turcay — Tue, 11 Jun 2019 17:07:53 GMT

Thank you for your response but I think we both need to control 12 month count and min and max yearmonth control. If there is 12 distinct yearmonth and have data set max(yearmonth) is not equal to Have2 table max(yearmonth)

It should be like following but becaust it is character variable it does not work. Can you help me about it?

proc sql noprint;
  select count(distinct(YEARMONTH)), min(YEARMONTH),max(YEARMONTH) into :countyear,:minyear, :maxyear from HAVE;
quit;
%PUT &countyear.;
%PUT &minyear.;
proc sql noprint;
  select max(YEARMONTH) into :maxyear from HAVE2;
quit;
%PUT &maxyear.;


data WANT;
  if &countyear=12 and max(YEARMONTH) ne "&maxyear" then set HAVE(where=(YEARMONTH ne "&minyear")) HAVE2;
  else set HAVE ;
run;

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

PaigeMiller — Tue, 11 Jun 2019 17:52:47 GMT

Thanks for the explanation. As far as I can see, there is nothing that requires macros or macro variables here, but as I will not have the time to work on this the rest of today, I will try to come up with something tomorrow.

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

turcay — Tue, 11 Jun 2019 18:18:52 GMT

Thank you I also tried to do with following way but it does not work;

proc sql;
create table have3 as
select * from have
union all
select * from have2 where YEARMONTH not in(select YEARMONTH from have) ;
quit;

proc sort data=have3;
by ID descending yearmonth;
run;

proc sql noprint;
  select count(distinct(yearmonth)) into :countyear from have3;
quit;
%put &countyear;

data have;
set have3;
by id ;
if not last.id and &countyear.>12;
Run;

any suggestions? @ChrisNZ @Ksharp @Reeza @novinosrin @LinusH @Kurt_Bremser @Quentin @SuryaKiran

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

Reeza — Tue, 11 Jun 2019 19:15:34 GMT

I think you're seriously over complicating this. Are you trying to ensure that you have a full 12 month for each ID and that it's the current month, not the last month? Please confirm if this is correct and if so, I'll post a revised solution.

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

Kurt_Bremser — Wed, 12 Jun 2019 06:57:44 GMT

If you want my help, please post

sufficient example data to illustrate your issue and all edge cases
the expected outcome
the rule(s) for producing that outcome

Re: How to Drop Earliest Date and Add the Latest Date into Data Set?

PaigeMiller — Wed, 12 Jun 2019 11:35:39 GMT

@Reeza wrote:
I think you're seriously over complicating this. Are you trying to ensure that you have a full 12 month for each ID and that it's the current month, not the last month? Please confirm if this is correct and if so, I'll post a revised solution.

@Reeza makes a very good point. Is it correct? You haven't given this explanation, and quite honestly, the more I think about what you said, the less clear I am on what you want. The explanation you have given doesn't seem general enough to program, except for the very specific original data set you provided, so its not difficult to provide a solution for that one original data set with 11 observations (and I have done that) but obviously you have a more general problem and we are not sure exactly what that more general problem is.