topic Data Step: by two groups (First.id and first.department) in SAS Programming

Data Step: by two groups (First.id and first.department)

Dynamike — Thu, 11 Oct 2018 09:59:03 GMT

Hi all,

i have the following:

I want to define a minimum regarding ID as main variable (which works).

ID MIN_DATE

1 01/02/2015

Proc Sort Data=have;
by ID YEAR MONTH;
run;

Data MIN;
set have;
by ID;
MIN_YEAR=YEAR;
MIN_MONTH=MONTH;
MIN_DATE=MDY(MIN_MONAT,1,MIN_JAHR);
format MIN_DATE ddmmyy10.;
If First.ID then output;
Run;

Now i want to define a minimum for ID as well as for another subvariable (X) and this should look like this:

ID X MIN_DATE

1 a 01/02/2015

1 b 01/04/2016

i tried this:

Proc Sort Data=have;
by ID X YEAR MONTH;
run;

Data MIN;
set have;
by ID X;
MIN_YEAR=YEAR;
MIN_MONTH=MONTH;
MIN_DATE=MDY(MIN_MONAT,1,MIN_JAHR);
format MIN_DATE ddmmyy10.;
If First.ID AND FIRST.X then output;
Run;

But my subvariable X is not considered.....its just the same as above.

Could you help me?

Best regards

Mike

Re: Data Step: by two groups (First.id and first.department)

Kurt_Bremser — Thu, 11 Oct 2018 09:36:33 GMT

Your code will not run, but throw an ERROR because versid is not in the by statement, therefore first.versid does not exist.

Re: Data Step: by two groups (First.id and first.department)

Dynamike — Thu, 11 Oct 2018 09:59:54 GMT

Sorry my fault: VERSID is the same as ID.

I have corrected it in the original post.

Thank you for your advice!

Re: Data Step: by two groups (First.id and first.department)

Kurt_Bremser — Thu, 11 Oct 2018 10:11:11 GMT

Your problem is the use of

first.id and first.x

as that will only be true for the first occurence of an id.

A reduced subsetting if will work:

data have;
input id x $ year month;
cards;
1 a 2018 1
1 a 2018 3
1 a 2018 2
1 b 2017 12
1 b 2017 3
;
run;

Proc Sort Data=have;
by ID X YEAR MONTH;
run;

Data MIN;
set have;
by ID X;
MIN_YEAR=YEAR;
MIN_MONTH=MONTH;
MIN_DATE=MDY(MIN_MONAT,1,MIN_JAHR);
format MIN_DATE ddmmyy10.;
If FIRST.X;
Run;

Re: Data Step: by two groups (First.id and first.department)

FreelanceReinh — Thu, 11 Oct 2018 10:20:55 GMT

... and make sure that you use consistent variable names (see MIN_YEAR vs. MIN_JAHR). To improve efficiency, you could move the three assignment statements MIN_...=... together with the OUTPUT statement into a DO-END block:

if first.x then do; ...; end;

or, even simpler, put "if first.x;" directly after the BY statement.

Re: Data Step: by two groups (First.id and first.department)

Tom — Thu, 11 Oct 2018 12:41:35 GMT

If you want to get one observation per ID,X group then just reference FIRST.X. If you include FIRST.ID you are looking for only one observation per ID group.

If you do include FIRST.ID there is no need to also include FIRST.X. When you are on the first observation for the current ID group you are by definition on the first observation for the current X group.