Help with arrays & loops

[wpgnbo]

I'm hoping for help with the following - thanks in advance for your advice!

I have a dataset of prescription records where each record = 1 prescription (in total there are ~23,000 records, representing about 900 individuals - so many individuals have multiple prescriptions, as one would expect).  How do I 'condense' this dataset to have only one record per individual? 

So, I have:

Person1 Rx1

Person1 Rx2

Person1 Rx3

Person2 Rx1

Person3 Rx1

Person3 Rx2

... etc..

But I want to have:

Person1 Rx1 Rx2 Rx3

Person2 Rx1

Person3 Rx1 Rx2

... etc.

I have a feeling this will involve arrays and do loops, but I don't know where to start.

Thanks!

[PGStats]

Assuming rx1, rx2, etc. are strings that you want to concatenate :

data want;

length rxString $200;

do until (last.person);

     set have; by person notsorted;

     rxString = catx(" ",rxString, rx);

     end;

drop rx;

run;

PG

[wpgnbo]

Thanks... I get an error, "Error 73-322: Expecting an =."  ... for the do until last.person line... what am I doing wrong?

[Linlin · Accepted solution]

Hi,

try changing

do until last.person;

to

do until (last.person);

[wpgnbo]

also... I should clarify that I need each prescription to be a different variable (rather than a long string separated by spaces).

So I need a "Person" variable, plus a collection of Rx variables (Rx1, Rx2, Rx3, Rx4...)  For a person with only 1 prescription, their Rx2, Rx3... fields would be blank.

[Linlin]

data have;

input (person rx) ($);

cards;

Person1 Rx1

Person1 Rx2

Person1 Rx3

Person2 Rx1

Person3 Rx1

Person3 Rx2

;

data want;

length rxString $200;

do until (last.person);

     set have; by person notsorted;

     rxString = catx(" ",rxString, rx);

     end;

drop rx;

proc print;run;

                           obs    rxString       person

                            1     Rx1 Rx2 Rx3    Person1

                            2     Rx1            Person2

                            3     Rx1 Rx2        Person3

[wpgnbo]

Right... Linlin, this code works, thank you.  But I wasn't clear with what I need.  I don't want a long string with all the Rx's listed... I want separate variables, more like:

obs      person      Rx1        Rx2        Rx3    

1         Joe           drugA     drugB     drugC

2          Fred         drugD

3          Mary          drugE     drugF     drugA

4          Sara          drugC     drugG     drugH

...

[Linlin]

Hi,

The proc transpose produces the exact output you requested in your last post.

[wpgnbo]

So it does, thank you Linlin!

[wpgnbo]

Thanks, that fixed the problem.  But now... I need to separate the long string into separate Rx variables.

We're getting there - thanks everyone for your help!

[Linlin]

data have;

input (person rx) ($);

cards;

Person1 Rx1

Person1 Rx2

Person1 Rx3

Person2 Rx1

Person3 Rx1

Person3 Rx2

;

proc transpose data=have out=want(drop=_:) prefix=rx;

var rx;

by person;

proc print;run;