Combining two subsequen observations

[DOBBINHO]

Hi all,

I have a sample dataset of which i'd like to check if the observation starts with '/gb/pro/', and if it does, I need to concatenate it to the end of the previous observation with a ' ' in between.

Kindly advice on how I could do so....

LINES

chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/tx.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/tx.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/vs.sas7bdat
/gb/pro/cli/dat/ba/102/009/stab/blin/le0/yc.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/vs.sas7bdat
/gb/pro/cli/dat/mb/102/009/stab/blin/le0/yc.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/ba/102/009/stab/blin/le0/ye.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/ba/102/009/stab/blin/le0/ye.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/ba/102/009/stab/blin/le0/yh.sas7bdat,old,xxxx,none,2/22/2008,9:51:7

[Reeza]

1. Use LAG to retrieve the previous value

2. Use =: to check if hte start of the variable matches your criteria

3. Use CATX() to concatenate the values.

Or since it looks like you're attempting to get file listings from an LS command, you could probably modify the code to generate the full file paths rather than doing this.

---

@DOBBINHO wrote:

Hi all,

I have a sample dataset of which i'd like to check if the observation starts with '/gb/pro/', and if it does, I need to concatenate it to the end of the previous observation with a ' ' in between.

Kindly advice on how I could do so....

LINES

chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/tx.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/tx.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/vs.sas7bdat
/gb/pro/cli/dat/ba/102/009/stab/blin/le0/yc.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/mb/102/009/stab/blin/le0/vs.sas7bdat
/gb/pro/cli/data/mb/102/009/stab/blin/level0/yc.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/mb/102/009/stab/blin/le0/ye.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/mb/102/009/stab/blin/level0/ye.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/mb/102/009/stab/blin/level0/yh.sas7bdat,old,xxxx,none,2/22/2008,9:51:7

---

[Astounding]

I think LAG will be clumsy here.  You really need to look ahead, not look back.  Here is an approach:

data want;

length line $2000;  /* long enough length to hold double current length */

set have end=done;

if not done then set have (firstobs=2 keep=line rename=(line=next_line));

if done=0 and next_line =: '/gb/pro' then line = catx(' ', line, next_line);

if line =: '/gb/pro' then delete;

drop next_line;

run;

[DOBBINHO]

set have end=done;

if not done then set have (firstobs=2 keep=line rename=(line=next_line));

if done=0 and next_line =: '/gb/pro' then line = catx(' ', line, next_line);

where is done coming from?

[Astounding]

It comes from the SET statement.  The END= option on a SET statement creates a variable, and automatically assigns it values of 0 or 1 to indicate whether that SET statement has read in all the observations available.

Take note ... it's an important tool to know about.

[mkeintz]

data want (drop=nxt_:);
  merge have
        have (firstobs=2 rename=(line=nxt_line));
  if nxt_line=:'/gb/pro' then line=catx(' ',line,nxt_line);
  if not(line=:'/gb/pro');
run;

[Reeza]

Oh...and you can modify an LS command to give you only paths from a certain folder.

[PeterClemmensen]

Something like this 

data have;
input string:$500.;
datalines;
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/tx.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/tx.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/vs.sas7bdat
/gb/pro/cli/dat/ba/102/009/stab/blin/le0/yc.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/dat/ba/102/009/stab/blin/le0/vs.sas7bdat
/gb/pro/cli/dat/mb/102/009/stab/blin/le0/yc.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/ba/102/009/stab/blin/le0/ye.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/ba/102/009/stab/blin/le0/ye.sas7bdat,new,add,read,2/22/2008,9:51:7
chad,hardy,/gb/pro/cli/data/ba/102/009/stab/blin/le0/yh.sas7bdat,old,xxxx,none,2/22/2008,9:51:7
;

data want(keep=string);
	merge have have(firstobs=2 rename=(string=nextstring));
	if substr(nextstring, 1, 8)="/gb/pro/" then
		string=cats(string, " ", nextstring);
	if (substr(nextstring, 1, 8)="/gb/pro/") | (substr(string, 1, 8) ne "/gb/pro/");
run;