Help in my data step code

[knveraraju91]

Dear,

I need help in my pgm. Please suggest in my code to get output. Thank you

For id=2 and 3, have missing visits. I need to have each subject all possible visits records.   

2  in this dataset.

output need;

id     visit                        visitn

1    Post1 Cycle 4          12
1     Post2 Cycle 4           18
2    Post1 Cycle 4           12
2     Post2 Cycle 4           18
3    Post1 Cycle 4            12
3    Post2 Cycle 4          18
4    Post1 Cycle 4           12
4    Post2 Cycle 4             18

data one;
input id visit $3-16 visitn;
datalines;
1 Post1 Cycle 4 12
1 Post2 Cycle 4 18
2 Post1 Cycle 4 12
3 Post2 Cycle 4 18
4 Post1 Cycle 4 12
4 Post2 Cycle 4 18
;

proc sql noprint;
  create table tmp1 as
   select distinct id,visit
   from one;
quit;
data tmp2;
 set tmp1;
     do visitn= 12, 18; output; end;
run;
data want;
 merge one tmp2;
  by id visit visitn;
run;

[mkeintz]

I usually try to resist atavistic impulses to use sql, but here is a situation that I think sql stands up well:

data one;
input id visit $3-16 visitn;
datalines;
1 Post1 Cycle 4 12
1 Post2 Cycle 4 18
2 Post1 Cycle 4 12
3 Post2 Cycle 4 18
4 Post1 Cycle 4 12
4 Post2 Cycle 4 18
;

proc sql;
  create table want as 
  select id, visit, visitn
  from 
  (select distinct id from one)
   cross join 
  (select distinct visit,visitn from one) ;
quit;

You can relatively easily expand this to more than 2 groups of variables.  In this case group1 is ID, group 2 is visit*visitn.

Editted change:

modified

  (select distinct id from one)
   join 
  (select distinct visit,visitn from one) on 1 ;

   to

  (select distinct id from one)
   cross join 
  (select distinct visit,visitn from one)  ;

[knveraraju91]

Thank you very much for your time. I am assuming this is 'left join' since you used 'join' statement only and also i have a one clarification 'on 1' statement. I tried online i could not get exact answer. Please clarify me . Thank you