DATA Step, Macro, Functions and more

reading raw data

Reply
Contributor
Posts: 45

reading raw data

 

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: 9,553

Re: reading raw data

Posted in reply to shivamarrora0

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,008

Re: reading raw data

Posted in reply to shivamarrora0

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]

Contributor
Posts: 45

Re: reading raw data

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: 9,553

Re: reading raw data

Posted in reply to shivamarrora0

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
Super Contributor
Posts: 260

Re: reading raw data

[ Edited ]
Posted in reply to KurtBremser

@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: 226

Re: reading raw data

Posted in reply to shivamarrora0

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

Ask a Question
Discussion stats
  • 6 replies
  • 116 views
  • 0 likes
  • 5 in conversation