topic Re: SAS Lag in New SAS User

SAS Lag

lisa2002 — Wed, 19 Jun 2019 16:41:42 GMT

SAS members I have a question and I hope someone can assist as soon as possible. I have a data set where I need to match up the duplicates while combing number, rx_number, start_date, end_date, sig, and rx_name in one column but separate each rx1 rx2 and so on while keeping the information for the patient together in one row... basically I need it to look like this

RX1 RX2

NS4747 - 123456 - tylenol - 5/7/2019 - 9/3/2019 - NS4747 - 123456 - dayquil - 5/7/2019 - 9/3/2019 -

TAKE 1 TABLET(S) ORALLY AT BEDTIME - TAKE 1 TABLET(S) ORALLY AT BEDTIME -

M.E - [PSYCHIATRY] M.E - [PSYCHIATRY]

Here is sample data

Patients Number RX_NUMBER START_DATE END_DATE SIG RX_NAME

John Doe NS4747 123456 6/16/19 6/18/19 TAKE 1 TABLET(S) ORALLY Tylenol

AT BEDTIME

Patients Number RX_NUMBER START_DATE END_DATE SIG RX_NAME

John Doe NS4747 123456 6/16/19 6/18/19 TAKE 1 TABLET(S) ORALLY dayquil

AT BEDTIME

Patients Number RX_NUMBER START_DATE END_DATE SIG RX_NAME

Jane Doe NS1234 78912 6/14/19 6/17/19 TAKE 1 TABLET(S) ORALLY claritin

AT BEDTIME

My code is causing the issue where the rx_number from John Doe is caring over to Jane Doe and so on... Please tell me what I am doing wrong.

Thank You. FEELING OVERWHELMED

Re: SAS Lag

ChrisBrooks — Tue, 18 Jun 2019 22:32:10 GMT

Hi @lisa2002 the first thing that strikes me is that you should never use the lag function inside a conditional statement. You have several instances of using lag() inside an if condition as you can get incorrect results. Your best plan is to do that lags first, store the values in variables, then use the if statement on those variables.

Re: SAS Lag

lisa2002 — Wed, 19 Jun 2019 16:44:33 GMT

ChrisBrooks
Can you help guide me in the direction I should go to receive the results I'm looking for?

Re: SAS Lag

Tom — Wed, 19 Jun 2019 16:55:01 GMT

Why do you think you need to use LAG at all?

If you are just transposes from multiple observations per patient to one observation per patient then there is no need for LAG(). So if patient X has 4 observations the resulting table will have RX1 to RX4 populated for this patient.

Personally I would just use one step to generate the concatenated string and use PROC TRANPOSE.

data step1;
  set have;
  length rx $2000 ;
  rx=catx(' - ',number,rx_number,.....);
run;
proc transpose data=step1 out=want prefix=rx;
  by patients;
  var rx;
run;

Or are you trying to collapse multiple records for the same drug into one? If so what is the criteria to know how to collapse the records?

Re: SAS Lag

lisa2002 — Wed, 19 Jun 2019 18:33:39 GMT

multiple records for the same PATIENT into one. So basically some patients have one prescription and some have 2 or more I need the the prescriptions to match the Patient on one row

Re: SAS Lag

ChrisBrooks — Thu, 20 Jun 2019 09:44:52 GMT

Hi @lisa2002 - it looks like you've edited your post removing the code fragment since I replied mentioning your use of the lag function. In any case this is what I meant by never using lag inside a conditional statement.

The issue with lag is that, contrary to what you might think, it doesn't return the value from the previous observation. Instead it returns the value from the last time the function was executed. Here's a simple example using the SASHELP.CLASS data set:

/* Assume we want to populate the variable called "Previous" */
/* with the value from the previous observation but ONLY */
/* if the current observation has the value "M" for the sex */
/* variable */


/* Incorrect way of using lag */

data lagged1;
	length previous $8.;
	set sashelp.class;
	if sex="M" then previous=lag(name);
run;

This is what the output looks like

You'll notice that for observation number 5 you'd expect the value of Previous to be "Carol" but actually it's "Alfred" - the last time the if condition was true.

This is the correct way to do it

data lagged2(drop=temp);
	length previous $8.;
	set sashelp.class;
	temp=lag(name);
	if sex="M" then previous=temp;
run;

The output is now

Now it works as desired because the lag function is executed outside the if condition and so always executes.