topic Re: reading raw data in SAS Programming

reading raw data

shivamarrora0 — Thu, 02 Nov 2017 14:06:51 GMT

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

Re: reading raw data

Kurt_Bremser — Thu, 02 Nov 2017 14:30:22 GMT

Look here for a similar question with solution.

Re: reading raw data

ballardw — Thu, 02 Nov 2017 14:33:47 GMT

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]

Re: reading raw data

shivamarrora0 — Thu, 02 Nov 2017 14:41:08 GMT

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

Re: reading raw data

Kurt_Bremser — Thu, 02 Nov 2017 14:42:37 GMT

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

Re: reading raw data

HB — Thu, 02 Nov 2017 16:48:48 GMT

@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

Re: reading raw data

s_lassen — Fri, 03 Nov 2017 08:20:36 GMT

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