BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas_lak
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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:

View solution in original post

5 REPLIES 5
Reeza
Super User

AAre there actually blanks in your data?

sas_lak
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1036 views
  • 4 likes
  • 4 in conversation