topic Re: Take first 10 patients (assigned as char) based on the first 10 that received a dose in date ord in SAS Programming

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

Suki99 — Wed, 28 Feb 2024 15:01:55 GMT

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

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

ballardw — Wed, 28 Feb 2024 15:25:09 GMT

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-data-AKA-generate/ta-p/258712 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.

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

SASJedi — Wed, 28 Feb 2024 15:32:24 GMT

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

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

PaigeMiller — Wed, 28 Feb 2024 16:53:02 GMT

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.

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

Suki99 — Wed, 28 Feb 2024 17:05:25 GMT

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

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

PaigeMiller — Wed, 28 Feb 2024 20:11:08 GMT

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

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

Sajid01 — Wed, 28 Feb 2024 20:51:07 GMT

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;

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

Patrick — Thu, 29 Feb 2024 02:10:06 GMT

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

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

SASJedi — Thu, 29 Feb 2024 13:05:17 GMT

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

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

Sajid01 — Thu, 29 Feb 2024 14:30:43 GMT

hanks @SASJedi for the update.