Need help with some SAS coding

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Need help with some SAS coding

Hi,

 

My data looks like this:

 

subject                visit            col1     col2    col3

 

1001                 week 1         xx        yy       zz

1001                 week 2         xx        yy       zz

1001                 week 3         xx        yy       zz

 

1002                 week 1        xx        yy       zz

1002                 week 2        xx        yy       zz

1002                 week 3        xx        yy       zz

1002                 week 4         xx        yy       zz

 

1003                 week 1       xx        yy       zz

1003                 week 4       xx        yy       zz

1003                 week 6       xx        yy       zz

1003                 week 7       xx        yy       zz

 

and so on.

 

I have total 7 visits (Week 1- Week 7).

 

So basically the problem is not all subjects have got all the visits. I want to create a dataset which should contain ALL subjects at EACH visit. 

 

I was trying cartesian product by PROC SQL.


proc sql;
create table tmp as
select a.*,b.visit
from (select subjid, col1 ,col2 ,col3
from data_a) as a ,
(select distinct visit
from data_a) as b
order by b.visit;
quit;

 

But that creates 7 new records for each observation. Ex- Subject 1001 is getting 3 x 7 = 21 rows in tmp.

I need only 7 rows for each subject.

 

Any help will be highly appreciated.

 

Kind regrads,

D


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 19,170

Re: Need help with some SAS coding

General easy method is to create an empty table with all the fields and merge it back in.

 

proc sql;
create table empty as
select * from
(select distinct subject from have
cross join
select distinct visit from have) as A
order by subject, visit;
quit;

proc sql;
create table want as
select a.subject, a.visit, b.col1, b.col2, b.col3
from empty as a
left join have as b
on a.subject=b.subject
and a.visit=b.visit
order by a.subject, a.visit;
quit;

 

View solution in original post


All Replies
Super User
Posts: 11,134

Re: Need help with some SAS coding

[ Edited ]

Are you visit values ACTUALLY week 1, week 2, or some other coding? I can see one solution that works with values like "week 1" that would not work if they coded differently.

The following assumes that the data is sorted by subject, order of visit and the values of visit are coded as "week 1" with one space an one digit. If the length varies such as using more spaces or values of more 7 then this will not work.

data want;
   set have;
   by subject;
   weeknum = input(substr(visit,6),best2.);
   if last.subject then do;
      output;
      if weeknum < 7 then do i= (weeknum+1) to 7;
         visit = cats('week',i);
         call missing(col1,col2,col3);
         output;
      end;
      drop i weeknum;
   end;
   else output;
run;
Contributor
Posts: 25

Re: Need help with some SAS coding

[ Edited ]

@ballardw

 

My week values actually go upto Week 52. starting with Baseline, then week 1, week 2,...week 52. I wrote upto week 7 to construct a simple example.

Super User
Posts: 11,134

Re: Need help with some SAS coding

Do  you have cases where you start at say Week 50 and go into the next calendar year as Week 1, Week 2 and such?

If so, it is time to start with an actual Date value to allow proper cross-year boundary interation without massive amounts of unneeded code to address what SAS will with the INTNX function.

Super User
Super User
Posts: 7,720

Re: Need help with some SAS coding

I would imagine from the text that Week xx is respective of the study week, and so nothing to do with week of year.  So week 30 would be week 30 regardles of being in December or January.   So I would agree with Reeza, create list of unique Subjects/Visits, then join back to original data. 

 

There is also another way, create a visitnum variable - i.e. use CDISC SDTM standards, then you can use this to create loops up to the current value and output from an array - note that you need to condsider what to put in other variables of this dataset, the below just outputs the same row each time with the new visit:

data have;
  input subject $ visit $;
datalines;
01234 Week1
01234 Week3
01234 Week4
01234 Week7
;
run;

data want;
  set have;
  visitnum=input(tranwrd(visit,"Week",""),best.);
run;

data want (drop=visit visitnum weeks1-weeks7 i j rename=(new_week=visit));
  set want;
  by subject;
  retain i;
  array weeks{7} $20. ("Week1","Week2","Week3","Week4","Week5","Week6","Week7");
  if first.subject then i=1;
  do j=i to visitnum;
    new_week=weeks{i};
    output;
    i=i+1;
  end;
run;

Contributor
Posts: 25

Re: Need help with some SAS coding

Agree with @RW9. The week has nothing to do with the month of the year
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 19,170

Re: Need help with some SAS coding

General easy method is to create an empty table with all the fields and merge it back in.

 

proc sql;
create table empty as
select * from
(select distinct subject from have
cross join
select distinct visit from have) as A
order by subject, visit;
quit;

proc sql;
create table want as
select a.subject, a.visit, b.col1, b.col2, b.col3
from empty as a
left join have as b
on a.subject=b.subject
and a.visit=b.visit
order by a.subject, a.visit;
quit;

 

Contributor
Posts: 25

Re: Need help with some SAS coding

Thanks a lot @Reeza

 

This actually works with a little tweak in Syntax.

 

proc sql;
create table empty as
select * from
(select distinct subject from have) as A
cross join
(select distinct visit from have) as B
order by subject, visit;
quit;

proc sql;
create table want as
select a.subject, a.visit, b.col1, b.col2, b.col3
from empty as a
left join have as b
on a.subject=b.subject
and a.visit=b.visit
order by a.subject, a.visit;
quit;

 

Respected Advisor
Posts: 3,788

Re: Need help with some SAS coding

You can simplify this greatly with the features of PROC SUMMARY.  You'll need a list of all VISIT values in the order that you want the new records to appear to use with PROC SUMMARY CLASSDATA option.

 

data visit;
   input subject:$4. visit&$    (col1-col3)($);
   cards; 
1001                 week 1         xx        yy       zz
1001                 week 2         xx        yy       zz
1001                 week 3         xx        yy       zz
1002                 week 1        xx        yy       zz
1002                 week 2        xx        yy       zz
1002                 week 3        xx        yy       zz
1002                 week 4         xx        yy       zz
1003                 week 1       xx        yy       zz
1003                 week 4       xx        yy       zz
1003                 week 6       xx        yy       zz
1003                 week 7       xx        yy       zz
;;;;
   run;
proc print;
   run;
data classdata;
   if _n_ eq 1 then set visit(keep=visit);
   w = scan(visit,1);
   do i = 1 to 10;
      visit = catx(' ',w,i);
      output;
      end;
   stop;
   keep visit;
   run;
proc print;
   run;
proc summary data=visit classdata=classdata nway;
   by subject;
   class visit / order=data;
   output out=filled(drop=_type_ rename=(_freq_=origin)) idgroup(out(col:)=);
   run;
proc print;
   run;


 Obs    subject     visit     origin    col1    col2    col3

   1     1001      week 1        1       xx      yy      zz 
   2     1001      week 2        1       xx      yy      zz 
   3     1001      week 3        1       xx      yy      zz 
   4     1001      week 4        0                          
   5     1001      week 5        0                          
   6     1001      week 6        0                          
   7     1001      week 7        0                          
   8     1001      week 8        0                          
   9     1001      week 9        0                          
  10     1001      week 10       0                          
  11     1002      week 1        1       xx      yy      zz 
  12     1002      week 2        1       xx      yy      zz 
  13     1002      week 3        1       xx      yy      zz 
  14     1002      week 4        1       xx      yy      zz 
  15     1002      week 5        0                          
  16     1002      week 6        0                          
  17     1002      week 7        0                          
  18     1002      week 8        0                          
  19     1002      week 9        0                          
  20     1002      week 10       0                          
  21     1003      week 1        1       xx      yy      zz 
  22     1003      week 2        0                          
  23     1003      week 3        0                          
  24     1003      week 4        1       xx      yy      zz 
  25     1003      week 5        0                          
  26     1003      week 6        1       xx      yy      zz 
  27     1003      week 7        1       xx      yy      zz 
  28     1003      week 8        0                          
  29     1003      week 9        0                          
  30     1003      week 10       0                          
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 416 views
  • 0 likes
  • 5 in conversation