topic Re: Removing first observation if it meets criteria within a specific data range in New SAS User

Removing first observation if it meets criteria within a specific data range

mmh — Fri, 03 Dec 2021 14:44:30 GMT

Dear altruists,
I am a new SAS user.
I have the following dataset (I have attached the SAS dataset in the end):

Company_ID	Date	Fiscal_Quarter
1210	2009-03-31	4
1210	2009-06-30	1
1210	2009-09-30	2
1094	2009-03-31	3
1094	2009-06-30	4
1094	2009-09-30	1
1327	2009-03-31	2
1327	2009-06-30	3
1327	2009-09-30	4
1045	2009-03-31	1
1045	2009-06-30	2
1045	2009-09-30	3
1120	2009-03-16	4
1120	2009-06-30	1
1120	2009-09-30	2

I want to create two output datasets on the following condition:
During the period from 15 March 2009 to 14 March 2010, if the first observation of a certain Company_ID has a Fiscal_Quarter value of 4, then this observation should be put in a new dataset.
All the remaining observations will be sent to another dataset.

So, the first output dataset would be as follows:

Company_ID	Date	Fiscal_Quarter
1210	2009-03-31	4
1120	2009-03-16	4

And the second output dataset would be as follows:

Company_ID	Date	Fiscal_Quarter
1210	2009-06-30	1
1210	2009-09-30	2
1094	2009-03-31	3
1094	2009-06-30	4
1094	2009-09-30	1
1327	2009-03-31	2
1327	2009-06-30	3
1327	2009-09-30	4
1045	2009-03-31	1
1045	2009-06-30	2
1045	2009-09-30	3
1120	2009-06-30	1
1120	2009-09-30	2

Thank you in advance for all your support!

Re: Removing first observation if it meets criteria within a specific data range

PaigeMiller — Fri, 03 Dec 2021 14:52:17 GMT

data first_output_data_set second_output_data_set;
    set have;
    by company_id notsorted;
    if first.company_id=4 then output first_output_data_set;
    else output second_output_data_set;
run;

This code is UNTESTED. If you want tested code, please provide (a portion of) the data as SAS data step code (instructions).

Re: Removing first observation if it meets criteria within a specific data range

mmh — Sat, 04 Dec 2021 08:48:17 GMT

Thank you so much for your reply.
Unfortunately, it is not working.
I am adding the datastep here for you:

data have;
input Company_ID $4. Date Fiscal_Quarter;
informat Date date9.;
format Date date9.;
cards;
1210 31mar2009 4
1210 30jun2009 1
1210 30sep2009 2
1094 31mar2009 3
1094 30jun2009 4
1094 30sep2009 1
1327 31mar2009 2
1327 30jun2009 3
1327 30sep2009 4
1045 31mar2009 1
1045 30jun2009 2
1045 30sep2009 3
1120 16mar2009 4
1120 30jun2009 1
1120 30sep2009 2
;
run;

Re: Removing first observation if it meets criteria within a specific data range

Kurt_Bremser — Sat, 04 Dec 2021 10:10:17 GMT

data
  want1
  want2
;
set have;
by company_id notsorted;
if first.company_id and fiscal_quarter = 4
then output want1;
else output want2;
run;

Re: Removing first observation if it meets criteria within a specific data range

mmh — Sat, 04 Dec 2021 10:52:19 GMT

Thank you so much Kurt!
I really appreciate your kind support!