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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.