BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Almoha
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Katie
Obsidian | Level 7

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;

View solution in original post

3 REPLIES 3
Katie
Obsidian | Level 7

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;

Ksharp
Super User
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;



Kurt_Bremser
Super User

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  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 913 views
  • 0 likes
  • 4 in conversation