Hi All,
ID NAME PH
---- ---- ----
1111 AAAA 99999
1111 AAAA 99999
1111 88888
1111 AAAA 77777
2222 BBBB 55555
2222 66666
2222 44444
I NEED TO ARRENGE THE DATA LIKE
WHEN ID AND NAME IS SAME AND PH NUMBER IS DIFFERNT
Out put should like
ID NAME PH
---- ---- ----
1111 AAAA 99999,88888,77777
2222 BBBB 55555,66666,44444
Thanks in advance.
As mentioned this is the datastep method:
data have;
infile datalines missover dsd dlm=',';
input iD NAME $ PH;
datalines;
1111,AAAA,99999
1111,AAAA,99999
1111,,88888
1111,AAAA,77777
2222,BBBB,55555
2222,,66666
2222,,44444
;
run;
data want (keep=id name_out ph_out);
set have;
length ph_out name_out $200.;
retain ph_out name_out;
by id;
if first.id then do;
ph_out=strip(put(ph,best.));
name_out=name; /* Added this to cover missing names */
end;
else if index(ph_out,strip(put(ph,best.)))=0 then ph_out=catx(',',ph_out,put(ph,best.));
if last.id then output;
run;
The below also gives you an intermediary table with distinct ph values and id/names where not null. This could be transposed or datastepped in the above manner:
AAre there actually blanks in your data?
Yes,
Actually blanks are there in orizinal data, but we can ignore the blank data.
My requirement is,- if ID is same and PH values are differnt need to arrenge the data as like
ID NAME PH
---- ---- ----
1111 AAAA 99999,88888,77777
2222 BBBB 55555,66666,44444
Thank u,
As mentioned this is the datastep method:
data have;
infile datalines missover dsd dlm=',';
input iD NAME $ PH;
datalines;
1111,AAAA,99999
1111,AAAA,99999
1111,,88888
1111,AAAA,77777
2222,BBBB,55555
2222,,66666
2222,,44444
;
run;
data want (keep=id name_out ph_out);
set have;
length ph_out name_out $200.;
retain ph_out name_out;
by id;
if first.id then do;
ph_out=strip(put(ph,best.));
name_out=name; /* Added this to cover missing names */
end;
else if index(ph_out,strip(put(ph,best.)))=0 then ph_out=catx(',',ph_out,put(ph,best.));
if last.id then output;
run;
The below also gives you an intermediary table with distinct ph values and id/names where not null. This could be transposed or datastepped in the above manner:
Yes, your logic does not match your data. Distinct ID, Name values would give 2 results for id 1111, one for AAAA one for missing.
Also, to do this is should be straight-forward:
proc sql; select distinct id,name,ph from have; quit;
Then you can either transpose and use catx(',',of ph);
Or just do it in a datastep with a retain concatenating the results and output at last.id.
data have; infile datalines missover dsd dlm=','; input iD NAME $ PH; datalines; 1111,AAAA,99999 1111,AAAA,99999 1111,,88888 1111,AAAA,77777 2222,BBBB,55555 2222,,66666 2222,,44444 ; run; data want (drop=name ph); set have; by id PH notsorted; length _name $ 20 _ph $ 200; retain _name _ph; if first.id then call missing(_name,_ph); if not missing(name) then _name=name; if first.ph then _ph=catx(',',_ph,ph); if last.id; run;
Xia Keshan
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 25. 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.