Moving to another varibale

[sas_Forum]

Moving to another variable

data temp;
input id1 id2 id3
cards;
1 2 3
  5 6
6 7
run;

if the id2 and id3 are not balank they should move to id1.

output
id1 id2 id3
1
2
3
5
6
6
7

[Ksharp]

How about:

data temp;
input id1 id2 id3 ;
cards;
1 2 3
. 5 6
6 7 .
run;
data want;
 set temp;
 array _id{*} id:;
 do i=1 to dim(_id);
  if not missing(_id{i}) then do;id1=_id{i};output;end;
 end;
 drop i;
run;
data want;
 set want;
 call missing(id2,id3);
run;

Ksharp

[sas_Forum]

Thqs ksharp but small modification  new variable add is also added but the add should come beside id1 only

data temp;

input id1 id2 id3 add$;

cards;

1 2 3 xyx

. 5 6 klm

6 7 . mno

run;

output
id1 id2 id3  add
1                xyz
2
3
5
6
6               mno
7

[Ksharp]

OK.

data temp;
input id1 id2 id3 add $;
cards;
1 2 3 xyz
. 5 6 lkj
6 7 . mnb
run;
data want;
 set temp;
 array _id{*} id:;
 do i=1 to dim(_id);
  if missing(id1) then call missing(add);
  if not missing(_id{i}) then do;id1=_id{i};output;call missing(add);end;
 end;
 drop i;
run;
data want;
 set want;
 call missing(id2,id3);
run;

Ksharp

[data_null__]

By adding an observation ID variable to use as a BY variable you can use PROC TRANSPOSE, and save yourself from all that fiddly stuff.  You don't get ID2 and ID3 in the output data set but since you want them to be all missing what's the point.

data tempV/view=tempv;
   set temp;
   _obs_ + 1;
   run;
proc transpose prefix=ID out=_data_(drop=_: where=(not missing(id1)));
   by _obs_;
   var id:;
   copy add;
   run;
proc print;
   run;

Obs    add    ID1
 1     xyz     1
 2             2
 3             3
 4             5
 5             6
 6     mnb     6
 7             7

[Tom]

If you are really reading them from raw data then use trailing @@ on the input statement.

data temp;

  input id @@;

cards;

1 2 3

  5 6

6 7

run;

[sas_Forum]

small change in the req now i have no and it should increment by 1 as i want to use no as a primary key...
data temp;
input id1 id2 id3 add $ no$;
cards;
1  2 3  xyz a
.   5  6  lkj b
6  7  .  mnb c
8  .  .   klm d
.  .  10  sdf e
run;

output:

id1 id2 id3 add  no
1            xyz   a_1
2                    a_2
3                    a_3
5           lkj      b_1
6                    b_2
6           mnb   c_1
7                    c_2
8           lkm    d
5           sdf     e

[Tom]

I would recommend making the new key variable a different name than the raw variable that you are inputing.

Also I am not sure why you want to remove the link to the value of the ADD variable on the extra rows.

This code should do what you want.

data temp;
  length key $10 id 8 ;
  input id1 id2 id3 add $ no$;
  array ids id1-id3 ;
  n=0;
  do i=1 to dim(ids);
    if ids(i) ne . then do;
      id = ids(i);
      n+1;
      if n > 1 then add=' ';
      key = catx('_',no,n);
      output;
    end;
  end;
  drop i n ;
cards;
1  2 3  xyz a
.   5  6  lkj b
6  7  .  mnb c
8  .  .   klm d
.  .  10  sdf e
run;
proc print width=min; run;

Obs  key  id   id1  id2  id3  add  no
 1   a_1    1    1    2    3   xyz   a
 2   a_2    2    1    2    3         a
 3   a_3    3    1    2    3         a
 4   b_1    5    .    5    6   lkj   b
 5   b_2    6    .    5    6         b
 6   c_1    6    6    7    .   mnb   c
 7   c_2    7    6    7    .         c
 8   d_1    8    8    .    .   klm   d
 9   e_1   10    .    .   10   sdf   e

[Ksharp]

data temp;
input id1 id2 id3 add $ no$;
cards;
1  2 3  xyz a
.   5  6  lkj b
6  7  .  mnb c
8  .  .   klm d
.  .  10  sdf e
;
run;
data _null_;
 set temp end=last;
 array _id{*} id:;
 if _n_ eq 1 then call execute('data want;retain id2 id3 " ";');
 count=0;
 do i=1 to dim(_id);
 if not missing(_id{i}) then do;
                               call execute('id1='||_id{i}||';');
                               count+1;
                               if count=1 then call execute('add="'||strip(add)||'";');
                               call execute('no="'||strip(no)||'_'||strip(count)||'";output;add=" ";');
                             end;
 end;
 call execute('call missing(of _all_);');
 if last then call execute('run;');
run;


 

Ksharp

[R_Win]

p1 add p2 p3 pkey
1 xyz 1 1 a
. lkj 5 5 b
6 mnb 7 . c
8 klm . . d
. sdf 10 . e

As from where the pan is coming for the pkey that pan last digit
should append to the primary key

output

p1 add p2 p3 pkey
1 xyz 1 1 a_1
1 xyz 1 1 a_2
1 xyz 1 1 a_3
5 lkj 5 5 b_2
5 lkj 5 5 b_3
6 mnb 7 . c_1
7 mnb 7 . c_2
8 klm . . d_1
10 sdf 10 . e_2

[Ksharp]

Sure. Of course.

data temp;
input p1 add $ p2 p3 pkey $;
cards;
1 xyz 1 1 a
. lkj 5 5 b
6 mnb 7 . c
8 klm . . d
. sdf 10 . e
;
run;
data _null_;
 set temp end=last;
 array _p{*} p1-p3;
 if _n_ eq 1 then call execute('data want;');
 count=0;
 do i=1 to dim(_p);
 count+1;
 call execute('p2='||p2||';');
 call execute('p3='||p3||';');
 call execute('add="'||strip(add)||'";');
 if not missing(_p{i}) then do;
                               call execute('p1='||_p{i}||';');
                               call execute('pkey="'||strip(pkey)||'_'||strip(count)||'";output;');
                             end;
 end;
 call execute('call missing(of _all_);');
 if last then call execute('run;');
run;

Ksharp