topic Re: data logic in SAS Programming

data logic

Almoha — Mon, 04 Apr 2016 17:14:57 GMT

Hello,

I have the following data structure and need to create a dataset based on the following , can you please help me to create a variable avalc for the above subjects based on the following condition Value = 'Y' if any two non-missing consecutive date are >=7 days apart when DOSE > 0, othervise 'N'

Subj EXSTDTC DOSE

111 2015-04-01 100

111 2015-04-09

111 2015-04-15 100

111 2015-04-22 100

222 2015-04-01 100

222 2015-04-09 100

333 2015-04-01 100

333 2015-04-09

444 2015-04-10 100

444 100

444 2015-04-20 100

Thanks in Advance

Re: data logic

Katie — Mon, 04 Apr 2016 17:42:17 GMT

This isn't the most elegant solution but I believe it gets you what you want. I also assumed that you wanted it BY subject. So that piece is in there as well.

I also worked around the date (the second HAVE dataset due to my SAS not being able to read the dates you provided).

DATA Have;
INPUT SUBJ 1-3 EXSTDTC $5-14 DOSE 16-18;
DATALINES;
111 2015-04-01 100
111 2015-04-09
111 2015-04-15 100
111 2015-04-22 100
222 2015-04-01 100
222 2015-04-09 100
333 2015-04-01 100
333 2015-04-09
444 2015-04-10 100
444 100
444 2015-04-20 100
;
RUN;

DATA HAVE;
SET HAVE (RENAME = (EXSTDTC = EXSTDTC_OLD));
YEAR = SUBSTR(EXSTDTC_OLD,1,4);
MONTH = SUBSTR(EXSTDTC_OLD,6,2);
DAY = SUBSTR(EXSTDTC_OLD,9,2);
EXSTDTC = MDY(MONTH,DAY,YEAR);
FORMAT EXSTDTC MMDDYY10.;
DROP YEAR MONTH DAY EXSTDTC_OLD;
RUN;

PROC SORT DATA = HAVE;
BY SUBJ;
RUN;

DATA HAVE2 WANT;
SET HAVE;
BY SUBJ;
LAG_DT = LAG(EXSTDTC);
IF FIRST.SUBJ THEN LAG_DT = .;
DIFF = EXSTDTC- LAG_DT;
IF DOSE NE . AND DIFF >= 7 THEN FLAG = 'Y';
ELSE FLAG = 'N';
OUTPUT HAVE2;
IF FLAG = 'Y' THEN OUTPUT WANT;
FORMAT LAG_DT MMDDYY10.;
RUN;

Re: data logic

Ksharp — Tue, 05 Apr 2016 03:10:00 GMT

What output do you want ?



DATA Have; 
INPUT SUBJ 1-3 EXSTDTC : yymmdd10. DOSE 16-18; 
format EXSTDTC  yymmdd10. ;
DATALINES; 
111 2015-04-01 100
111 2015-04-09
111 2015-04-15 100
111 2015-04-22 100
222 2015-04-01 100
222 2015-04-09 100
333 2015-04-01 100
333 2015-04-09
444 2015-04-10 100
444 100
444 2015-04-20 100
; 
RUN;
data want;
 set have;
 if SUBJ = lag(SUBJ) and dif(EXSTDTC) ge 7 and DOSE gt 0 then avalc ='Y';
  else avalc ='N';
 run;

Re: data logic

Kurt_Bremser — Tue, 05 Apr 2016 06:28:27 GMT

How about this:

data have;
infile cards dsd dlm=' ' missover;
input subj exstdtc:yymmdd10. dose;
format exstdtc yymmdd10.;
cards;
111 2015-04-01 100
111 2015-04-09
111 2015-04-15 100
111 2015-04-22 100
222 2015-04-01 100
222 2015-04-09 100
333 2015-04-01 100
333 2015-04-09
444 2015-04-10 100
444  100
444 2015-04-20 100
;
run;

proc sort
  data=have (where=(exstdtc ne . and dose > 0))
  out=int
;
by subj exstdtc;
run;

data want;
set int;
by subj;
if not first.subj and intck('days',lag(exstdtc),exstdtc) > 7
then avalc = 'Y';
else avalc = 'N';
run;

proc print;
run;

Result:

                                             Obs    subj       exstdtc    dose    avalc

                                              1      111    2015-04-01     100      N  
                                              2      111    2015-04-15     100      Y  
                                              3      111    2015-04-22     100      N  
                                              4      222    2015-04-01     100      N  
                                              5      222    2015-04-09     100      Y  
                                              6      333    2015-04-01     100      N  
                                              7      444    2015-04-10     100      N  
                                              8      444    2015-04-20     100      Y