topic Re: How to arrenge the data in proper format in SAS Programming

How to arrenge the data in proper format

sas_lak — Mon, 15 Dec 2014 08:06:32 GMT

Hi All,

ID NAME PH

---- ---- ----

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.

Re: How to arrenge the data in proper format

Reeza — Mon, 15 Dec 2014 08:41:17 GMT

AAre there actually blanks in your data?

Re: How to arrenge the data in proper format

RW9 — Mon, 15 Dec 2014 09:03:08 GMT

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.

Re: How to arrenge the data in proper format

sas_lak — Mon, 15 Dec 2014 09:08:39 GMT

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,

Re: How to arrenge the data in proper format

RW9 — Mon, 15 Dec 2014 09:57:37 GMT

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:

Re: How to arrenge the data in proper format

Ksharp — Mon, 15 Dec 2014 13:18:44 GMT

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