DATA Step, Macro, Functions and more

Possibly a use for RETAIN . . .

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Possibly a use for RETAIN . . .

I am working with a transaction type data set where there are multiple observations for individual children.  Each child has a unique "child_id" and there are multiple observations, for which the child has an associated value for the variable FUNDING.  There may or may not be repeats for the values of FUNDING.  My main goal is to eliminate consecutive, repeating FUNDING values and then transpose the data set so that I have a list of "paths." There may be instances in which a funding source such as C appears as the second and last funding source.  How can I capture this, but also delete the consecutive, repeating instances of the funding source C?


There are around 80,000 children. There an indefinite number of TREATMENT paths based on around 30 TREATMENT types.


The following is similar to what the data currently looks like:


Child          Funding

1                    A

1                    C

1                    C

1                    D   

1                    C

2                    B

2                    B

2                    A

2                    B


The following is just a brief example of what I would like the data to look like. The TREATMENT should only increment when the treatment type has changed.

Child    Funding_1      Funding_2     Funding_3     Funding_4     Funding_n

   1            A                         C                     D                    C                    

   2            B                         A                     B              

Thank you for your help.


Accepted Solutions
Solution
‎11-08-2014 07:52 PM
Respected Advisor
Posts: 4,173

Re: Possibly a use for RETAIN . . .

Posted in reply to PhillipSherlock

Something like below should do the job.

data have;

  input child $ treatment $;

  datalines;

1 A

1 C

1 C

1 D  

1 C

2 B

2 B

2 A

2 B

;

run;

data prep; 

  set have;

  by child treatment notsorted;

  if first.treatment;

run;

proc transpose data=prep out=want prefix=Treatment_;

  by child notsorted;

  var treatment;

run;

View solution in original post


All Replies
Solution
‎11-08-2014 07:52 PM
Respected Advisor
Posts: 4,173

Re: Possibly a use for RETAIN . . .

Posted in reply to PhillipSherlock

Something like below should do the job.

data have;

  input child $ treatment $;

  datalines;

1 A

1 C

1 C

1 D  

1 C

2 B

2 B

2 A

2 B

;

run;

data prep; 

  set have;

  by child treatment notsorted;

  if first.treatment;

run;

proc transpose data=prep out=want prefix=Treatment_;

  by child notsorted;

  var treatment;

run;

Occasional Contributor
Posts: 14

Re: Possibly a use for RETAIN . . .

Patrick,

I edited my initial post to highlight my dilemma.  A funding source such as 'C' may appear and then reappear for a particular child.  I believe your procedure, like my previous attempts does not account for this.

Best,

Phil

Respected Advisor
Posts: 4,173

Re: Possibly a use for RETAIN . . .

Posted in reply to PhillipSherlock

Hi Phil

The code I've posted creates your desired result. What do you believe I'm missing?

It is the "notsorted" in the by statement which allows to transform stuff like C C A C to C A C - so the code I've posted will only get rid of consecutive duplicates and also won't change the sequence of the original data. Isn't this what you've asked for?

Thanks

Patrick

Occasional Contributor
Posts: 14

Re: Possibly a use for RETAIN . . .

You're right. I apologize for that. Thank you for your help.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 235 views
  • 0 likes
  • 2 in conversation