topic Re: Help in my data step code in SAS Programming

Help in my data step code

knveraraju91 — Mon, 31 Aug 2020 23:03:08 GMT

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;

Re: Help in my data step code

mkeintz — Tue, 01 Sep 2020 03:24:44 GMT

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 ;

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

Re: Help in my data step code

knveraraju91 — Tue, 01 Sep 2020 03:44:36 GMT

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