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
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.
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;
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.
@ballardw Thank you sir. I really need to get my eyes tested. Time to wear glasses 😞
@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.
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 🙂
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.