How to arrenge the data in proper format

[sas_lak]

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.

[Reeza]

AAre there actually blanks in your data?

[sas_lak]

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,

[RW9 · Accepted solution]

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:

[RW9]

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.

[Ksharp]

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