topic Re: Moving to another varibale in SAS Programming

Moving to another varibale

sas_Forum — Thu, 13 Oct 2011 06:51:43 GMT

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

Moving to another varibale

Ksharp — Thu, 13 Oct 2011 07:02:06 GMT

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

Moving to another varibale

sas_Forum — Thu, 13 Oct 2011 07:32:53 GMT

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

Moving to another varibale

Ksharp — Thu, 13 Oct 2011 08:37:46 GMT

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

Re: Moving to another varibale

data_null__ — Thu, 13 Oct 2011 12:08:15 GMT

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

Re: Moving to another varibale

Tom — Thu, 13 Oct 2011 13:31:23 GMT

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;

Re: Moving to another varibale

sas_Forum — Fri, 14 Oct 2011 13:57:37 GMT

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

Re: Moving to another varibale

Tom — Fri, 14 Oct 2011 15:05:49 GMT

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

Re: Moving to another varibale

Ksharp — Tue, 18 Oct 2011 04:56:54 GMT

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

Re: Moving to another varibale

R_Win — Tue, 18 Oct 2011 13:05:54 GMT

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

Re: Moving to another varibale

Ksharp — Wed, 19 Oct 2011 07:09:33 GMT

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