i have data like below
request parent number
123 234 789
123 234 545
want to merge like
request parent number
123 234 789/545
how to code this?
I always get the feeling that the generalizations I make when the problem is shown with a very small number of records (in this case 2 records) will not hold for larger data sets. So it would certainly help if you provided a larger data set with more conditions. Nevertheless, what you ask for is provided as follows:
data a;
input request parent number;
cards;
123 234 789
123 234 545
;
proc transpose data=a out=b;
by request parent;
run;
data want;
set b;
number = catx('/',col1,col2);
drop _name_ col1 col2;
Run;
Here are the two most common methods:
*create sample data for demonstration; data have; infile cards dlm='09'x; input OrgID Product $ States $; cards; 1 football DC 1 football VA 1 football MD 2 football CA 3 football NV 3 football CA ; run; *Sort - required for both options; proc sort data=have; by orgID; run; **********************************************************************; *Use RETAIN and BY group processing to combine the information; **********************************************************************; data want_option1; set have; by orgID; length combined $100.; retain combined; if first.orgID then combined=states; else combined=catx(', ', combined, states); if last.orgID then output; run; **********************************************************************; *Transpose it to a wide format and then combine into a single field; **********************************************************************; proc transpose data=have out=wide prefix=state_; by orgID; var states; run; data want_option2; set wide; length combined $100.; combined=catx(', ', of state_:); run;
https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a
@radha009 wrote:
i have data like below
request parent number
123 234 789
123 234 545
want to merge like
request parent number
123 234 789/545
how to code this?
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!
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.