SAS Lag

[lisa2002]

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

[ChrisBrooks · Accepted solution]

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.

[lisa2002]

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

[ChrisBrooks]

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.

[Tom]

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?

[lisa2002]

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