Take first 10 patients (assigned as char) based on the first 10 that received a dose in date order

[Suki99]

hi

In PROC SQL , assuming the dataset is called  exposure,  and we have 20 plus patients in the dataset what would be the best   proc sql code please?

thanks

[ballardw]

Since SQL does not generally process in records in an predictable order then SQL is a poor choice for anything that requires "first" anything.

Best is to provide an example of the type of data that you have, describe how to recognize "first 10" (not always obvious to other folks) and expected output. Data can be dummy but should behave like your actual data set. Best is to provide example data in the form of a working data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[SASJedi]

I'd use PROC FedSQL because it supports the LIMIT clause. Like this:

/* Make fake data to work with */
data WORK.EXPOSURE;
  infile datalines dsd truncover;
  input Patient:$200. DoseDate:MMDDYY10.;
  format DoseDate MMDDYY10.;
datalines4;
"Thomas, Katherin",01/05/2024
"Feldmann, Anthony",01/19/2024
"Tennyson, Sam",01/09/2024
"Curry, Kristi",01/10/2024
"Smith, Wanda",01/16/2024
"Hoadley, James",01/04/2024
"Wenzel, Calvin",01/30/2024
"Ryan, Jennifer",01/18/2024
"Reyes, Ricky",01/22/2024
"Attwood, James",01/05/2024
"Kaye, Echo",01/03/2024
"Christia, Anna",01/15/2024
"Cheeks, Linda",01/30/2024
"Yates, Agnes",01/04/2024
"Snider, Regina",01/05/2024
"Robinson, Nichole",01/12/2024
"Ramirez, Kevin",01/20/2024
"Carolan, Kris",01/20/2024
"Jenkins, Thomas",01/21/2024
"Tillman, Micheal",01/28/2024
"Gibbs, Wayne",01/10/2024
"Profit, Ronald",01/15/2024
"Morrell, Suzanna",01/28/2024
"Snelling, Miguel",01/23/2024
"Hutching, Randall",01/16/2024
"Schweitzer, Veronica",01/25/2024
"McDonald, Nathan",01/12/2024
"Kelley, Robert",01/03/2024
"Carroll, John",01/16/2024
"Shipley, Lewis",01/03/2024
;;;;

/* Create the report */
proc FedSQL number;
select Patient, DoseDate
	from exposure
	order by DoseDate
	limit 10
;
quit;

Result:

Row	Patient	DoseDate
1	Shipley, Lewis	01/03/2024
2	Kaye, Echo	01/03/2024
3	Kelley, Robert	01/03/2024
4	Hoadley, James	01/04/2024
5	Yates, Agnes	01/04/2024
6	Thomas, Katherin	01/05/2024
7	Snider, Regina	01/05/2024
8	Attwood, James	01/05/2024
9	Tennyson, Sam	01/09/2024
10	Gibbs, Wayne	01/10/2024

[Suki99]

Hi SAS Jedi

I forgot to mention  , a patient can have multiple dose dates  , ie future visits. So i would need a group of some sort.

thanks

[PaigeMiller]

---

@Suki99 wrote:

I forgot to mention  , a patient can have multiple dose dates  , ie future visits. So i would need a group of some sort.

---

Explain this in more detail. Explain how this affects the input data. Explain how this affects the desired output.

[Patrick]

---

@Suki99 wrote:

Hi SAS Jedi

I forgot to mention  , a patient can have multiple dose dates  , ie future visits. So i would need a group of some sort.

thanks

---

Interpreting your statements that you just want to consider the earliest date per patient and that you don't care about ties below amended code from @SASJedi should do.

proc FedSQL number;
  select Patient, min(DoseDate) as DoseDate
	from work.exposure
  group by patient
	order by DoseDate, Patient
	limit 10
;
quit;

If you do care about ties then consider code as below. Depending on how you choose to deal with ties (ties=...) the result will of course differ.

proc sql;
  create view work.v_inter as
  select patient, min(DoseDate) as DoseDate format=date9.
  from work.have
  group by Patient
  order by DoseDate
  ;
quit;

proc rank data=v_inter ties=dense out=work.ranked(where=(rank<=10));
  var DoseDate;
  ranks rank;
run;

proc print data=ranked;
run;

[PaigeMiller]

What should be done if the 10th and 11th dates are equal? Should the output have 10 observations, or 11?

Other than that, this is easy to do if you drop the (in my opinion, wrong-headed) requirement to use PROC SQL.

proc sort data=exposure out=exposure_sorted(obs=10);
    by date;
run;

Also very easy to provide an answer if there are ties and you need to select more than 10 (because there are many records with the same date).

Please note that 3 people have responded and none wants to provide an SQL solution. It is the wrong tool here. 

[Sajid01]

Hello @Suki99 
You can use Proc fedSQL as suggested by @SASJedi if you have SAS Viya.
However, if you are using SAS 9.4 and don't have FedSQL, you can try the following. The output table will have the  first 10 ruws from the  result set. I have used the data from the post by @SASJedi 

proc sql outobs=10;
create table work.want as
select * from exposure 
order by dosedate;
quit;

[SASJedi]

@Sajid01 PROC FedSQL is part of Base SAS since the release of SAS 9.4 in 2013. If you have access to SAS 9.4, you can use PROC FedSQL.

[Sajid01]

hanks @SASJedi  for the update.