DATA Step, Macro, Functions and more

Moving to another varibale

Reply
Frequent Contributor
Posts: 140

Moving to another varibale

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

Super User
Posts: 9,681

Moving to another varibale

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

Frequent Contributor
Posts: 140

Moving to another varibale

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

Super User
Posts: 9,681

Moving to another varibale

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

Respected Advisor
Posts: 3,777

Re: Moving to another varibale

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

Super User
Super User
Posts: 6,500

Re: Moving to another varibale

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;

Frequent Contributor
Posts: 140

Re: Moving to another varibale

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

Super User
Super User
Posts: 6,500

Re: Moving to another varibale

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

Super User
Posts: 9,681

Re: Moving to another varibale

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

Regular Contributor
Posts: 229

Re: Moving to another varibale

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

Super User
Posts: 9,681

Re: Moving to another varibale

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

Ask a Question
Discussion stats
  • 10 replies
  • 285 views
  • 0 likes
  • 5 in conversation