BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wpgnbo
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

Hi,

try changing

do until last.person;

to

do until (last.person);

View solution in original post

10 REPLIES 10
PGStats
Opal | Level 21

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
wpgnbo
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

Hi,

try changing

do until last.person;

to

do until (last.person);

wpgnbo
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

Hi,

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

wpgnbo
Calcite | Level 5

So it does, thank you Linlin!

wpgnbo
Calcite | Level 5

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
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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