Combine Fields From Dataset

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Combine Fields From Dataset

Greetings!

I am using SAS EG 5.1.  I have created a dataset that gives me all data from open issues that are pending for my team to work.  What happens though, is that because one of the field, which is a free text note field, I get duplicate each time a different employee worked the same issue.  Some of the issues have duplicate entries and some do not.

What i am trying to figure out is how to combine all of the notes into one entry so i eliminate the rows and have all the notes that the employees did into one field.

Here is my code... and I am not sure which function in SAS to use to get there.  Any help would be stellar!

Proc SQL noprint;

connect to oracle (user='**********' orapw='**********' path=***** preserve_comments);

create table work.MSG_QUEUES as select * from connection to oracle

  ( SELECT DISTINCT MQ.ODR_ODR_NUMBER,

    CG.GROUP_CODE,

        MQ.QUE_ID,

    MQ.QUE_TYPE,

    MQ.QUE_SOURCE,

    MQ.WORKED_FLAG,

    MQ.USR_MDFD,

    MQ.DT_MDFD,

    MQ.LAST_WORKED_DATE,

    MQ.STATUS,

    MQ.CALL_BACK_REQUIRED_FLAG,

    MQ.CALL_BACK_TIME,

    sp.scratch_pad   /* this is the field i want to do this with */

  FROM   ATS_MASTER_QUEUES      MQ,

    ATS_ORDERS             AO,

    ATS_CPY_GROUP_CHANNEL  CG,

    ATS_ORD_SCRATCH_PAD    SP

  WHERE QUE_TYPE IN ('ASK','ANS','REJ')

   AND AO.DT_CRTD >= trunc(to_date(%str(%'&SYSDATE9%'),'ddmonyyyy'))-160

   AND SP.DT_CRTD >= trunc(to_date(%str(%'&SYSDATE9%'),'ddmonyyyy'))-60

   AND MQ.ODR_ODR_NUMBER = SP.ODR_ODR_NUMBER :smileyplus:

   AND MQ.ODR_ODR_NUMBER = AO.ODR_NUMBER

   AND AO.CPY_ABBR = CG.CPY_ABBR :smileyplus:

   order by mq.odr_odr_number

  );

Disconnect from Oracle;

Quit;


Accepted Solutions
Solution
‎11-29-2013 03:42 PM
Super User
Posts: 19,869

Re: Combine Fields From Dataset

*Add in a line to keep only the last record, which is the one with all comments appended/concatenated together.;

data work.temp2;

length thisone $1000.;

set work.UNWORKED_MSGQ;

by ODR_ODR_NUMBER;

retain thisone;

if first.ODR_ODR_NUMBER then thisone=' ';

drop scratch_pad;

thisone= catx(' ', thisone, scratch_pad);

if last.ODR_ODR_NUMBER then output;

run;

View solution in original post


All Replies
Super User
Posts: 19,869

Re: Combine Fields From Dataset

I don't think there's a way to combines a field across rows in a SQL step unless you use a subquery or something else.


The function you're looking for is to concatenate though. 

Contributor
Posts: 57

Re: Combine Fields From Dataset

You have multiple observations in different rows.  You'll need to collapse the table ATS_ORD_SCRATCH_PAD first so that all orders appears in a single row by order number.  Then you perform the merge.  Here is an example of collapsing the table (I made up a dataset to resemble your oracle table ATS_ORD_SCRATCH_PAD.

data work.temp;

   format ODR_ODR_NUMBER best8. DT_CRTD MMDDYY10. scratch_pad $1000.;

   infile datalines delimiter=',';

   input ODR_ODR_NUMBER DT_CRTD MMDDYY10. scratch_pad $;

   datalines;

   1,11/21/2013,These are notes on the first job

   1,11/22/2013,Second job's notes here

   2,11/22/2013,first job here

   2,11/22/2013,second employee's notes

   1,11/25/2013,final set of notes

;

run;  

proc sort data=work.temp;

by ODR_ODR_NUMBER DT_CRTD;

run;

data work.temp2;

length thisone $1000.;

set work.temp;

by ODR_ODR_NUMBER;

retain thisone;

if first.ODR_ODR_NUMBER then thisone=' ';

drop scratch_pad;

thisone= catx(' ', thisone, scratch_pad);

run;

Now just perform your SQL Join but use the work.temp2 table instead of the ATS_ORD_SCRATCH_PAD table.

Contributor
Posts: 70

Re: Combine Fields From Dataset

Posted in reply to statistician13

So, I have been playing with this code... and i am a little confused.

I think the piece i am missing is in the first bit of code.  I am not sure how to coincide this with the actual table.

I ran the bottom two programs and i got it to run but it still made the duplicate entries.  I basically want the notes to roll up into one row and I am not sure that the code was doing that.  However, I am a little out of my league on this one.

Dean

Super User
Posts: 19,869

Re: Combine Fields From Dataset

Post the code you used that didn't work, that was modified from post.

Contributor
Posts: 70

Re: Combine Fields From Dataset

Here is the code i was using:

proc sort data=work.UNWORKED_MSGQ;

by ODR_ODR_NUMBER DT_CRTD;

run;

data work.temp2;

length thisone $1000.;

set work.UNWORKED_MSGQ;

by ODR_ODR_NUMBER;

retain thisone;

if first.ODR_ODR_NUMBER then thisone=' ';

drop scratch_pad;

thisone= catx(' ', thisone, scratch_pad);

run;

Solution
‎11-29-2013 03:42 PM
Super User
Posts: 19,869

Re: Combine Fields From Dataset

*Add in a line to keep only the last record, which is the one with all comments appended/concatenated together.;

data work.temp2;

length thisone $1000.;

set work.UNWORKED_MSGQ;

by ODR_ODR_NUMBER;

retain thisone;

if first.ODR_ODR_NUMBER then thisone=' ';

drop scratch_pad;

thisone= catx(' ', thisone, scratch_pad);

if last.ODR_ODR_NUMBER then output;

run;

Contributor
Posts: 57

Re: Combine Fields From Dataset

Reeza is correct.  I'm so sorry.  The last line of my code "if last.odr_odr_number then output" was someone accidentally left out of my code.  Once you do this, you should be all set!

Contributor
Posts: 70

Re: Combine Fields From Dataset

I finally achieved success with the following code:

proc sort data=work.UNWORKED_MSGQ out=sorted;

by odr_odr_number dt_crtd;

run;

data concat;

length total_notes $ 10000 /* need to be the max */;

     set sorted;

     by odr_odr_number dt_crtd;

     retain total_notes;

     if first.odr_odr_number then total_notes = scratch_pad;

     else total_notes=trim(total_notes) || ' ' || trim(scratch_pad);

     DROP SCRATCH_PAD;

     if last.odr_odr_number then output;

run;

Your code ran as well Reeza... thanks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 286 views
  • 4 likes
  • 3 in conversation