First date of a group

[FilipAxelsson]

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-08-08      2022-08-08

3     2022-10-15      2022-08-08

Do someone have a solution to this? 

Best regards Filip

[andreas_lds]

Are the dates sas-dates or strings?

[FilipAxelsson]

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

[ballardw]

---

@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.

[FilipAxelsson]

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

[PeterClemmensen]

Is this a Visual Analytics question?

[FilipAxelsson]

Yes this is a SAS VA question

[Kurt_Bremser]

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;

[FilipAxelsson]

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

[acordes]

The DOW technique

[Kurt_Bremser]

---

@acordes wrote:
The DOW technique

---

Exactly. Learned it here on the communities.

[Reeza]

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

[FredrikE]

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

[FilipAxelsson]

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!