Help with arrays & loops

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Help with arrays & loops

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!


Accepted Solutions
Solution
‎11-27-2012 07:08 PM
Super Contributor
Posts: 1,636

Re: Help with arrays & loops

Hi,

try changing

do until last.person;

to

do until (last.person);

View solution in original post


All Replies
Respected Advisor
Posts: 4,934

Re: Help with arrays & loops

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

PG
Occasional Contributor
Posts: 9

Re: Help with arrays & loops

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

Solution
‎11-27-2012 07:08 PM
Super Contributor
Posts: 1,636

Re: Help with arrays & loops

Hi,

try changing

do until last.person;

to

do until (last.person);

Occasional Contributor
Posts: 9

Re: Help with arrays & loops

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.

Super Contributor
Posts: 1,636

Re: Help with arrays & loops

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

Occasional Contributor
Posts: 9

Re: Help with arrays & loops

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

...

Super Contributor
Posts: 1,636

Re: Help with arrays & loops

Hi,

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

Occasional Contributor
Posts: 9

Re: Help with arrays & loops

So it does, thank you Linlin!

Occasional Contributor
Posts: 9

Re: Help with arrays & loops

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!

Super Contributor
Posts: 1,636

Re: Help with arrays & loops

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=_Smiley Happy prefix=rx;

var rx;

by person;

proc print;run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 312 views
  • 6 likes
  • 3 in conversation