topic Help with arrays & loops in SAS Programming

Help with arrays & loops

wpgnbo — Tue, 27 Nov 2012 23:29:39 GMT

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!

Re: Help with arrays & loops

PGStats — Tue, 27 Nov 2012 23:45:21 GMT

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;

Re: Help with arrays & loops

wpgnbo — Wed, 28 Nov 2012 00:00:20 GMT

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

Re: Help with arrays & loops

Linlin — Wed, 28 Nov 2012 00:08:06 GMT

Hi,

try changing

do until last.person;

do until (last.person);

Re: Help with arrays & loops

wpgnbo — Wed, 28 Nov 2012 00:10:35 GMT

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.

Re: Help with arrays & loops

wpgnbo — Wed, 28 Nov 2012 00:13:20 GMT

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!

Re: Help with arrays & loops

Linlin — Wed, 28 Nov 2012 00:15:43 GMT

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

Re: Help with arrays & loops

Linlin — Wed, 28 Nov 2012 00:19:56 GMT

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;

Re: Help with arrays & loops

wpgnbo — Wed, 28 Nov 2012 00:20:25 GMT

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

...

Re: Help with arrays & loops

Linlin — Wed, 28 Nov 2012 14:04:09 GMT

Hi,

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

Re: Help with arrays & loops

wpgnbo — Thu, 29 Nov 2012 23:53:55 GMT

So it does, thank you Linlin!