- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have a dataset that is read in from a control file that lists visit IDs, names, and windows as well as anchor datasets if applicable. I then merge this with eCRF data and external lab data to compare visit dates to make sure they are within windows. My concern is that there may be more than one anchor dataset, and I want to be able to get a distinct list of anchor datasets where applicable, but I'm not sure how to go about this. Below is my current thought process, but this is a specific example. I need to generalize my code inside a macro to fit any number of anchor datasets. I think my biggest concern is how to get the maximum number of "words" in the ANCHOR variable so that I can loop through it. There may even be a more efficient way to do it than what I have below.
In short, below is the code to get a sample dataset. I want to take the ANCHOR variable and get a list of distinct values, which in this case would be RD_DOSEL, RD_DOSEL2, RD_DOSEL3, RD_DOSEL4, RD_DOSEL5. I can then use this to loop inside a macro to read each dataset.
data viswin;
input VISITID 1-5 VISITMNEMONIC $ 9-24 MIN_WINDOW 25-27 MAX_WINDOW 29 ANCHOR $ 33-63;
cards;
30101 Day 1 (Part 1) 0 0 n/a
30115 Day 15 (Pat 1) -2 2 n/a
30199 SFUP (Part 1) -7 7 RD_DOSEL, RD_DOSEL2, RD_DOSEL3
30201 Day 1 (Part 2) 0 0 n/a
30215 Day 15 (Part 2) -2 2 n/a
30299 SFUP (Part 2) -7 7 RD_DOSEL, RD_DOSEL4
30301 Day 1 (Part 3) 0 0 n/a
30315 Day 15 (Part 3) -3 3 n/a
30399 SFUP (Part 3) -7 7 RD_DOSEL, RD_DOSEL5
;
run;data anchor; set viswin (where=(anchor^='n/a')); ndset=countw(anchor); dset1=scan(anchor,1); dset2=scan(anchor,2); dset3=scan(anchor,3);run;data anchor2; set anchor (keep=dset1 rename=(dset1=dset)) anchor (keep=dset2 rename=(dset2=dset)) anchor (keep=dset3 rename=(dset3=dset)); if dset='' then delete;run;proc sort data=anchor2 nodupkey; by dset;run;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Okay. I have come up with a piece of generalized code. But can someone review this and let me know if there is a more efficient way to do this? The code just seems a little lengthy for what I'm trying to do.
%macro anchor;
data anchor;
set viswin (where=(anchor^='n/a'));
ndset=countw(anchor);
run;
proc sql noprint;
select max(ndset) into :nanchor from anchor;
%let nanchor=%sysfunc(compress(&nanchor.));
quit;
data anchor;
set anchor;
%do i=1 %to &nanchor.;
dset&i.=scan(anchor,&i.);
%end;
run;
data anchor2;
set %do i=1 %to &nanchor.;
anchor (keep=dset&i. rename=(dset&i.=dset))
%end; ;
if dset='' then delete;
run;
proc sort data=anchor2 nodupkey;
by dset;
run;
proc sql noprint;
select count(*) into :ndset from anchor2;
%let ndset=%sysfunc(compress(&ndset.));
select dset into :dset1-:dset&ndset. from anchor2;
quit;
%mend anchor;
%anchor;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry the code seems to be unreadable.
* split multiple anchors in one record per value; data anchor; set viswin (where=(anchor^='n/a')); do i = 1 to countw(anchor); dset=scan(anchor,i); output; end; run; proc sql noprint; * noprint to avoid output-display from second select; * get distinct list; select distinct dset into :anchor_list separated by ' ' from anchor; * count; select count(distinct(dset)) into: anchor_count from anchor; quit; %let anchor_count = &anchor_count; * to get rid of blanks; %put &=anchor_list; %put &=anchor_count;