Hi all, i need to solve the below issue
Data is
id include exclude
a . abc
a . xyz
b j abf
b . gtj
Output required is
a . abc,xyz
b j abf,gtj
Look here for a similar question with solution.
Please describe rules.
Is the rule to have all of the "Include" values separated by commas within each value of id as a single variable?
If the value of Include duplicates within ID is each instance included or only one?
How many distinct values of "include" might be involved? The variable to hold the values should be given a length long enough to hold all of them. The length would be (length of variable include)*(number of include values)+(number of include values minus 1) [the plus is to account for the lengths of the inserted commas]
rule is i want the data of 1 st raw
then last value of 2nd raw
then 3rd row full and last value of 4th row
@shivamarrora0 wrote:
rule is i want the data of 1 st raw
then last value of 2nd raw
then 3rd row full and last value of 4th row
Really? Will your input dataset always consist of exactly four observations?
Trying to use Kurt's solution posted above
data mydata;
input id:$1. include:$1. exclude:$3.;
datalines;
a . abc
a . xyz
b j abf
b . gtj
;
run;
data newdata;
set mydata;
retain myinclude concat ;
by id;
length myinclude $10.;
length concat $10.;
if first.id then myinclude = "";
if first.id then concat = "";
myinclude = catx('',myinclude,include);
concat = catx(',',concat,exclude);
if last.id then output;
drop include exclude;
run;
I can make
The SAS System | ||
id | myinclude | concat |
a | abc,xyz | |
b | j | abf,gtj |
Here is a possible solution:
data want; do until(last.id); set have; by id; length max_include $1 concat $20; call catx(',',concat,exclude); if max_include<include then max_include=include; end; drop exclude include; run;
But it is not quite clear what you want if you have multiple, different non-blank values for INCLUDE. If you want to concatenate them, like with exclude, change the if...then section to a CATX call like for exclude (and remember to change the length and name of the output variable), if you just want the first non-blank value, you can use the COALESCEC() function.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.