Combining two subsequen observations

Reply
Contributor
Posts: 20

Combining two subsequen observations

[ Edited ]

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

Super User
Posts: 21,530

Re: Combining two subsequen observations

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.

 

delete_lag.JPG


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


 

Super User
Posts: 5,995

Re: Combining two subsequen observations

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;

Contributor
Posts: 20

Re: Combining two subsequen observations

Posted in reply to Astounding

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?

 

Super User
Posts: 5,995

Re: Combining two subsequen observations

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.

Trusted Advisor
Posts: 1,148

Re: Combining two subsequen observations

 

 

 

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;
Super User
Posts: 21,530

Re: Combining two subsequen observations

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

PROC Star
Posts: 1,074

Re: Combining two subsequen observations

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;
Ask a Question
Discussion stats
  • 7 replies
  • 145 views
  • 1 like
  • 5 in conversation