Help with data manipulation

alaxman — Thu, 10 Dec 2020 04:00:47 GMT

Hello,

I am having some trouble working with my data. Hoping to get some help from the community.

Context:

In the original table below, starting with row #2 'svcdate_shifted' is computed as 'svcdate_shifted + Daysupp' from previous row. For row #1 -> svcdate_old = svcdate_shifted

Original Table1

Patient	ProdNme	Svcdate_old	Scvdate_shifted	Daysupp
B	BupA	9/29/14	9/29/14	7
B	BupA	10/6/14	10/6/14	30
B	Nal	10/9/14	11/5/14	30
B	Nal	10/16/14	12/5/14	5
B	Nal	10/31/14	12/10/14	30
B	BupA	11/18/14	1/9/15	7
B	BupA	11/24/14	1/16/15	30
B	Nal	12/24/14	2/15/15	30

I want to create the New Table below by manipulating data as follows:

When we encounter a change in prescription (BUP to Nal or vice versa) , if svcdate_old for Nal row < svcdate_shifted + daysupp (from previous row), then we recompute DaySupp (for the previous row) as Svcdate_old (from Nal row) - svcdate_shifted (from previous row). Hence the DaySupp in this New Table is now 3 for row #2 and is 5 for row #5
The Svcdate_shifted continues to be computed as 'svcdate_shifted + Daysupp' from previous row

Big picture: Any time the patient gets a new prescription (I.e BUP or Nal) represented by svcdate_old, we assume that they stop taking the previous prescription (and start with the new prescription) and hence the DaySupp value needs to be changed to represent that switch

New Table1

Patient	ProdNme	Svcdate_old	Scvdate_shifted	Daysupp
B	BupA	9/29/14	9/29/14	7
B	BupA	10/6/14	10/6/14	3
B	Nal	10/9/14	10/9/14	30
B	Nal	10/16/14	11/8/14	5
B	Nal	10/31/14	11/13/14	5
B	BupA	11/18/14	11/18/14	7
B	BupA	11/24/14	11/25/14	30
B	Nal	12/26/14	12/25/14	30

Just to present a different order of data, look at Original Table2 is as below:

Original Table2

Patient	ProdNme	Svcdate_old	Scvdate_shifted	Daysupp
B	BupA	9/29/14	9/29/14	7
B	BupA	10/6/14	10/6/14	30
B	Nal	10/9/14	11/5/14	30
B	BupA	10/4/14	12/5/14	6
B	Nal	10/16/14	12/11/14	5
B	Nal	10/31/14	12/16/14	30
B	BupA	11/18/14	1/15/15	7
B	BupA	11/24/14	1/22/15	30
B	Nal	12/24/14	2/21/15	30

I want to recreate new table 2 as follows with the same criteria that I listed in the earlier part of the post

New Table2

Patient	ProdNme	Svcdate_old	Scvdate_shifted	Daysupp
B	BupA	9/29/14	9/29/14	7
B	BupA	10/6/14	10/6/14	3
B	Nal	10/9/14	10/9/14	2
B	BupA	10/11/14	10/11/14	5
B	Nal	10/16/14	10/16/14	5
B	Nal	10/31/14	10/21/14	28
B	BupA	11/18/14	11/18/14	7
B	BupA	11/24/14	11/25/14	29
B	Nal	12/24/14	12/24/14	30

I originally had the below code, which worked when I only had 1 Nal visit & I purposefully stopped counting at that visit (I deleted all observations after this first Nal visit). However, I need to amend this code to now consider the different scenario above.

%macrohop(d,x,y,j);

_p_ = _n_ + &j;

if (1le _p_ le _o_) then set &d(keep=&x rename=(&x=&y)) point=_p_ nobs=_o_;

%mendhop;

data pers_3M_12M_30DAY (drop=TEMP1);

set pers_3M_12M_30DAY;

if prodnme = "NAL"

and svcdate_new le lag(svcdate_new)+ lag(daysupp)

thendo;

%hop(pers_3M_12M_30DAY,SVCDATE_NEW, TEMP1,-1)

DAYSUPP_NEW= SVCDATE-TEMP1;

end;

run;

%MACROHop1(d, x, y, j);

_p_=_n_ + &j;

IF(1<= _p_ <= _o_)THEN

DO;

_Found=1;

SET&d(KEEP=&x RENAME=(&x=&y)) POINT=_p_ NOBS=_o_;

END;

ELSE

DO;

_Found=0;

END;

%MENDHop1;

datapers_3M_12M_30DAY ;

setpers_3M_12M_30DAY;

%Hop1(pers_3M_12M_30DAY, ProdNme, _Next_ProdNme, 1);

IF_FoundTHEN

IF_Next_ProdNme = "NAL"

THEN

%Hop1(pers_3M_12M_30DAY, DAYSUPP_NEW, _Next_DAYSUPP_NEW, 1);

IF_Next_DAYSUPP_NEW ne .thendo;

daysupp = _Next_DAYSUPP_NEW;

drop_:;

end;

run;

Re: Help with data manipulation

ChrisNZ — Thu, 10 Dec 2020 04:47:06 GMT

Shouldn't your macros start with

do;

and end with

end;

Re: Help with data manipulation

Kurt_Bremser — Thu, 10 Dec 2020 08:45:30 GMT

Please supply example data in data steps with datalines, so we can recreate your datasets with a simple copy/paste and submit. Your tables are very hard to convert to code.

Re: Help with data manipulation

ballardw — Fri, 11 Dec 2020 15:05:39 GMT

Please look at the highlighted word below that appears in a snippet of your code:

and svcdate_new le lag(svcdate_new)+ lag(daysupp)
thendo;
    %hop(pers_3M_12M_30DAY,SVCDATE_NEW, TEMP1,-1)
         DAYSUPP_NEW= SVCDATE-TEMP1;
end;

You use that "thendo" repeatedly. This is not valid SAS syntax and will throw very obvious errors.

Or you are copying code from some source that when pasted to the forum is mangling your spacing.

Please post code in text box opened with the </> to preserve formatting. Or proofread very closely that what you think you pasted is what actually appears in the forum. The forum main message window will reformat text and depending on your source the results can be unpredictable. You have lots of places where there should be spaces and they don't appear. So we can't tell what may actually be code errors or paste issues.

topic Re: Help with data manipulation in SAS Programming

Help with data manipulation

Re: Help with data manipulation

Re: Help with data manipulation

Re: Help with data manipulation