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
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
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
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
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
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;
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
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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: