topic First date of a group in SAS Visual Analytics

First date of a group

FilipAxelsson — Tue, 17 Jan 2023 09:48:57 GMT

Hi, i want to extract the first date of a group, trying to use IF(...) combinded with min(..) but that doesn't seem to work with date. I want the following:

ID DATE WANTED

1 2022-01-01 2001-01-01

1 2022-08-10 2001-01-01

1 2001-01-01 2001-01-01

2 2020-05-02 2018-01-03

2 2018-01-03 2018-01-03

3 2022-08-08 2022-08-08

3 2022-10-15 2022-08-08

Do someone have a solution to this?

Best regards Filip

Re: First date of a group

andreas_lds — Tue, 17 Jan 2023 10:10:10 GMT

Are the dates sas-dates or strings?

Re: First date of a group

FilipAxelsson — Tue, 17 Jan 2023 10:52:24 GMT

I want them as SAS-dates since i then want to calculate the amount of days from the first date until now.

Re: First date of a group

PeterClemmensen — Tue, 17 Jan 2023 11:21:16 GMT

Is this a Visual Analytics question?

Re: First date of a group

FilipAxelsson — Tue, 17 Jan 2023 12:16:09 GMT

Yes this is a SAS VA question

Re: First date of a group

Kurt_Bremser — Tue, 17 Jan 2023 14:25:20 GMT

I can give you a Base SAS code solution:

data want;
format wanted yymmddd10.;
wanted = '31dec9999'd;
do until (last.id);
  set have;
  by id;
  wanted = min(wanted,date);
end;
do until (last.id);
  set have;
  by id;
  output;
end;
run;

Re: First date of a group

FilipAxelsson — Tue, 17 Jan 2023 14:44:13 GMT

That is sas code, im looking for a solution in SAS VA, Visual Analytics

Re: First date of a group

ballardw — Tue, 17 Jan 2023 15:49:45 GMT

@FilipAxelsson wrote:
I want them as SAS-dates since i then want to calculate the amount of days from the first date until now.

Does not answer the question. "Want" is not the response to an "are they" question.

The reason the question about if the values are currently date values is that if they are then they are numeric and query asking for the MINIMUM value by group would work. Character values, depending on how they are created may not work consistently with aggregates like Minimum.

Re: First date of a group

Reeza — Tue, 17 Jan 2023 16:37:03 GMT

Create a summary table with the ID and minimum of date field.
Merge that back in with the original table by ID.

PS Assuming you're using Data Studio - Prepare Data

Re: First date of a group

FilipAxelsson — Wed, 18 Jan 2023 08:19:04 GMT

Yes I understand that, and I misread the question but I thought I replayed again and clarified it was sas-date.

Re: First date of a group

FredrikE — Thu, 19 Jan 2023 10:07:39 GMT

Could it work by create numeric variables of the dates and then use the aggregate table function to get min/max per id, and then calculate the days?
BR
Fredrik

Re: First date of a group

FilipAxelsson — Thu, 19 Jan 2023 10:31:04 GMT

I haven’t tried that, it might work. Then the min function in the aggregated table have to work in a different way than the min function. But I will definitely try this!

Re: First date of a group

acordes — Wed, 25 Jan 2023 16:51:45 GMT

The DOW technique

Re: First date of a group

Kurt_Bremser — Wed, 25 Jan 2023 19:55:07 GMT

@acordes wrote:
The DOW technique

Exactly. Learned it here on the communities.