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
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;
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;
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;
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
Calling all data scientists and open-source enthusiasts! Want to solve real problems that impact your company or the world? Register to hack by August 31st!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.