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

have a source dataset that I need to get a column of distinct values and then "pivot" out the associated columns into a single new field.

 

Data source;

SomeID        Acct   Prod

ABC            1          Z

ABC           2           X

ACC           3           X

ADD            6          C

 

Results desired;

SomeID          new field

ABC               1,Z;2,X

ACC               3,X

ADD               6,C

 

Thanks,

 

SAS EG 7.13

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@novinosrin

OP shows semicolons between the "row groups"

 

data want;
   set have;
   by someid;
   length new $50;
   retain new;
   if first.someid then new=catx(',',acct,prod);
   else new=catx(';',new,catx(',',acct,prod));
   if last.someid;
run;

And of course NEW may need to have a length defined to hold much more text than the example data provides.

 

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

I hope this helps

 

data have;
input SomeID   $     Acct   Prod $;
cards;
ABC            1          Z
ABC           2           X
ACC           3           X
ADD            6          C
;

data want;
set have;
by someid;
length new $50;
retain new;
if first.someid then new=catx(',',acct,prod);
else new=catx(',',new,acct,prod);
if last.someid;
run;
ballardw
Super User

@novinosrin

OP shows semicolons between the "row groups"

 

data want;
   set have;
   by someid;
   length new $50;
   retain new;
   if first.someid then new=catx(',',acct,prod);
   else new=catx(';',new,catx(',',acct,prod));
   if last.someid;
run;

And of course NEW may need to have a length defined to hold much more text than the example data provides.

 

novinosrin
Tourmaline | Level 20

@ballardw Thank you sir. I really need to get my eyes tested. Time to wear glasses 😞

ballardw
Super User

@novinosrin wrote:

@ballardw Thank you sir. I really need to get my eyes tested. Time to wear glasses 😞


I was trying to figure out just what this could be useful for so was looking for different delimiters.

novinosrin
Tourmaline | Level 20

LOL Believe it or not, This isn't the first time that you brilliantly pointed out every bits of detail to correct me and I can't appreciate enough. I even told that to my parents(my mom in particular who taught me how to program using hash objects) and she opined "not to throw up codes when you are hung over, pay attention to every detail like @ballardw does" 

There you go 🙂

ballardw
Super User

Can you describe exactly how that new field will be used in other processes? If you are doing much other  than printing the result things get real ugly real fast.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 958 views
  • 1 like
  • 3 in conversation