topic Re: Find next variable by group in SAS Programming

Find next variable by group

sasuser_8 — Thu, 17 Oct 2024 15:24:38 GMT

Hi,

I have this code:

DATA SALE;
LENGTH FILE $3 DEPARTMENT $10 ;
INPUT FILE DATE:YYMMDD10. DEPARTMENT $;
FORMAT DATE YYMMDD10.;
DATALINES;

102 2024-01-15 RECEPTION
102 2024-01-30 RECEPTION
102 2024-02-10 SHOES
102 2024-02-29 RECEPTION 
102 2024-02-29 SHOES
103 2024-01-22 RECEPTION
103 2024-02-15 RECEPTION
103 2024-02-20 RECEPTION
103 2024-03-02 PANTS
104 2024-04-12 RECEPTION
104 2024-05-02 RECEPTION
104 2024-05-20 RECEPTION
104 2024-05-30 SHIRTS
104 2024-06-14 SHIRTS
104 2024-06-30 SHIRTS 
;
RUN;

I would like to find, for each file, the first department where the item was transferred after Reception. For that example, the result would be as follows:

Does anyone know how to proceed ?

Thanks

Re: Find next variable by group

data_null__ — Thu, 17 Oct 2024 16:58:44 GMT

This might work but I doubt you example data covers all scenarios.

DATA SALE;
LENGTH FILE $3 DEPARTMENT $10 ;
INPUT FILE DATE:YYMMDD10. DEPARTMENT $;
FORMAT DATE YYMMDD10.;
DATALINES;
102 2024-01-15 RECEPTION
102 2024-01-30 RECEPTION
102 2024-02-10 SHOES
102 2024-02-29 RECEPTION 
102 2024-02-29 SHOES
103 2024-01-22 RECEPTION
103 2024-02-15 RECEPTION
103 2024-02-20 RECEPTION
103 2024-03-02 PANTS
104 2024-04-12 RECEPTION
104 2024-05-02 RECEPTION
104 2024-05-20 RECEPTION
104 2024-05-30 SHIRTS
104 2024-06-14 SHIRTS
104 2024-06-30 SHIRTS 
;
RUN;

data sale;
   set sale;
   by file;
   ddif = dif(date);
   if first.file then ddif=.;
   run;
proc print;
   run;
proc summary data=sale nway;
   by file;
   where department ne: 'REC' and ddif ne 0;
   class department;
   output out=test(drop=_:) idgroup(min(ddif) out[1](date ddif)=);
   run;
proc print;
   run;

Re: Find next variable by group

Kurt_Bremser — Thu, 17 Oct 2024 21:06:56 GMT

data want;
set sales,,
by file;
length next_department $10 date_transfer 8;
format date_transfer yymmdd10.;
retain next_department date_transfer;
if first.file
then do;
  next_department = "";
  date_transfer = .;
end;
if department ne "RECEPTION" and next_department = ""
then do;
  next_department = department;
  date_transfer = date;
end;
if last.file;
keep file next_department date_transfer;
run;

Untested, posted from my tablet.

Re: Find next variable by group

Tom — Thu, 17 Oct 2024 21:54:31 GMT

Do you mean you want the first observation (that is not "RECEPTION') for each FILE? That does not sound like it needs to do anything with NEXT observation. Which makes it easier since it is easier to remember something than predict the future.

data want;
  set sale;
  by file date ;
  if first.file then any=0;
  retain any;
  if department ne 'RECEPTION' then do;
    if not any then output;
    any=1;
  end;
  drop any;
run;

Result

Obs    FILE    DEPARTMENT          DATE

 1     102       SHOES       2024-02-10
 2     103       PANTS       2024-03-02
 3     104       SHIRTS      2024-05-30

Re: Find next variable by group

Astounding — Thu, 17 Oct 2024 23:06:20 GMT

Given that your observations are in date order for each FILE, this should be easy:

data want;
   set SALE;
   by file;
   where department ne "RECEPTION";
   if first.file;
run;

The WHERE statement performs subsetting before first.file gets created.

Re: Find next variable by group

mkeintz — Fri, 18 Oct 2024 01:22:21 GMT

I think you want one record per FILE, namely the first non-reception record to follow a reception:

DATA SALE;
LENGTH FILE $3 DEPARTMENT $10 ;
INPUT FILE DATE:YYMMDD10. DEPARTMENT $;
FORMAT DATE YYMMDD10.;
DATALINES;
102 2024-01-15 RECEPTION
102 2024-01-30 RECEPTION
102 2024-02-10 SHOES
102 2024-02-29 RECEPTION 
102 2024-02-29 SHOES
103 2024-01-22 RECEPTION
103 2024-02-15 RECEPTION
103 2024-02-20 RECEPTION
103 2024-03-02 PANTS
104 2024-04-12 RECEPTION
104 2024-05-02 RECEPTION
104 2024-05-20 RECEPTION
104 2024-05-30 SHIRTS
104 2024-06-14 SHIRTS
104 2024-06-30 SHIRTS 
RUN;

data want;
  set sale;
  by file; 
  if first.file^=1 and department^='RECEPTION' and lag(department)='RECEPTION';
  if file^=lag(file);
run;

The first subsetting IF gets all non-receptions that follow a reception.

From that subset, the second subsetting IF takes only the first such record for each FILE.

Re: Find next variable by group

Ksharp — Fri, 18 Oct 2024 02:41:14 GMT

DATA SALE;
LENGTH FILE $3 DEPARTMENT $10 ;
INPUT FILE DATE:YYMMDD10. DEPARTMENT $;
FORMAT DATE YYMMDD10.;
DATALINES;
102 2024-01-15 RECEPTION
102 2024-01-30 RECEPTION
102 2024-02-10 SHOES
102 2024-02-29 RECEPTION 
102 2024-02-29 SHOES
103 2024-01-22 RECEPTION
103 2024-02-15 RECEPTION
103 2024-02-20 RECEPTION
103 2024-03-02 PANTS
104 2024-04-12 RECEPTION
104 2024-05-02 RECEPTION
104 2024-05-20 RECEPTION
104 2024-05-30 SHIRTS
104 2024-06-14 SHIRTS
104 2024-06-30 SHIRTS 
;
data want;
 set sale;
 by FILE;
 retain found 0;
 if first.FILE then found=0;
 if lag(DEPARTMENT) eq 'RECEPTION' and DEPARTMENT ne 'RECEPTION' and lag(FILE)=FILE and not found then do;
  output; found=1;
 end;
 drop found;
run;

Re: Find next variable by group

Kurt_Bremser — Fri, 18 Oct 2024 08:20:44 GMT

Elegant.

Re: Find next variable by group

Ksharp — Fri, 18 Oct 2024 08:55:02 GMT

Kurt,
Nope. If there were not any "RECEPTION" within a FILE group, that could get wrong result.

Re: Find next variable by group

Astounding — Fri, 18 Oct 2024 13:39:06 GMT

@Ksharp ,

Of course you're right about that. And other cases as well: if the first RECEPTION record comes after a non-RECEPTION record.

My real point: since the poster doesn't say what's really in the data, and it makes such a difference in the complexity of the solution, shouldn't somebody have asked? The original poster probably doesn't see that the question is important, so let me ask:

For a FILE, does the first record have to be RECEPTION or could it be something else?

If it is something else, should that record be ignored?

Re: Find next variable by group

sasuser_8 — Fri, 18 Oct 2024 16:35:07 GMT

Yes, the first record have to be RECEPTION. I didn't specify it and it's true that it's important. So your solution will work 🙂 Thanks !

Re: Find next variable by group

Ksharp — Sat, 19 Oct 2024 06:39:14 GMT

Astounding,
Don't be offended. I truely understand what you mean.
We all provide some suggestion based on the data posted by OP . We don't know how it looks like or how it was in reality.
So base on the data posted by OP and write code to get what OP want, That is OK.
My real point: make my own code to be as strong/robust as it could be to take into acount of many accident scenarios .

All the best.