BookmarkSubscribeRSS Feed
afernandezj
Calcite | Level 5

Hi,

I'm looking for a way to compare data between rows, and only show the distinct values, the idea is to create a repot that shows the all the possible contact numbers. I have the data from multiple sources and the same number is used as another contact number. The idea is to have all the unique contact numbers.

Is there a way to achieve this?, any help would  be appreciated

Example of the data:

 

idph1ph2ph3ph4ph5
1111111
1222
1222
2333
2444555
2555
3666
3777
3888888

Desired Output

idph1ph2ph3ph4ph5
1111222
2444333555
3666888777
7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Mmm, seems to be a lot of these questions at the moment.  You are struggling due to the nature of the thinking.  The data does not need to be transposed in this way, you will find life much easier in a normalised table.   Consider this, the second datastep creates a normalised structure with the results going down the page.  This can then easily be nodupkey sorted to give the unique values.

data have;

  retain id ph1-ph5;

  id=1;

    ph1=111;

    ph4=111;

    output;

    ph1=.;

    ph4=.;

    ph3=222;

    output;

    ph2=222;

    output;

run;

data want (keep=id res);

  set have;

  array ph{5};

  do i=1 to 5;

    if ph{i} ne . then do;

      res=ph{i};

      output;

    end;

  end;

run;


proc sort data=want nodupkey;

  by id res;

run;

afernandezj
Calcite | Level 5

Hi, thank you for your response, I had that though and I was able to convert the data to use the same column for the phone number, and another column to display the type of phone number:

Example:

idnumbertype
1111ph1
1222ph2
1111ph5
2333ph3
2444ph4

My problem here is removing the duplicate numbers, and since every ID has at least 3 numbers, I have a lot of data. The result was would not fit on an excel spreadsheet.

Is there a way to "clean" the data in this format?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why does the type matter?  In your example in the first post you take 222 from ph3, why not from ph2?  Doesn't seem to make sense to me.  You basically just want to find a list of unique numbers per id, so drop type and nodupkey sort it, i.e. id=1 only has 2, 1 is 111, the other 222.

On the second point, you don't really want to use Excel, I mean what reason would you have.  PDF for output, CSV or XML for transfer.

afernandezj
Calcite | Level 5

I'm being asked to display the type of contact information, for example if it is the house number, a cell, fax, etc, etc.

If there is a duplicate number, I should take just one, it does not matter witch type I choose.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

So, just proc sort, nodupkey, by id and number.  This will give you 1 row per unique id+number, with a ype (probably the first which appears in the dataset.

afernandezj
Calcite | Level 5

Thank you, I'm generating the information, so I can try what you suggested. I will let you know of the results.

afernandezj
Calcite | Level 5

Using the proc sort nodupkey by id and number, i was able to get 1 row, per id and number, Thanks!

I'm still removing numbers the users type just to fill in the fields, then I will need to transpose the result.

Thank you for your help!

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
  • 7 replies
  • 678 views
  • 0 likes
  • 2 in conversation