## DATA Step, Macro, Functions and more

Contributor
Posts: 45

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

Super User
Posts: 10,530

Look here for a similar question with solution.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,889

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]

Contributor
Posts: 45

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

Super User
Posts: 10,530

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 266

[ Edited ]

@KurtBremser

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

PROC Star
Posts: 270

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.

Discussion stats
• 6 replies
• 124 views
• 0 likes
• 5 in conversation