BookmarkSubscribeRSS Feed
shivamarrora0
Obsidian | Level 7

 

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

6 REPLIES 6
ballardw
Super User

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]

shivamarrora0
Obsidian | Level 7

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

Kurt_Bremser
Super User

@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?

HB
Barite | Level 11 HB
Barite | Level 11

@Kurt_Bremser

 

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

 

 

s_lassen
Meteorite | Level 14

@shivamarrora0:

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.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

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
  • 881 views
  • 0 likes
  • 5 in conversation