BookmarkSubscribeRSS Feed
sas_Forum
Calcite | Level 5

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

10 REPLIES 10
Ksharp
Super User

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
Calcite | Level 5

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
Super User

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__
Jade | Level 19

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Super User
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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1242 views
  • 0 likes
  • 5 in conversation