topic Re: Combining two subsequen observations in SAS Programming

Combining two subsequen observations

DOBBINHO — Thu, 21 Dec 2017 15:58:54 GMT

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

Re: Combining two subsequen observations

Reeza — Thu, 21 Dec 2017 16:06:02 GMT

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

Re: Combining two subsequen observations

Astounding — Thu, 21 Dec 2017 16:25:39 GMT

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;

Re: Combining two subsequen observations

Reeza — Thu, 21 Dec 2017 16:29:06 GMT

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

Re: Combining two subsequen observations

DOBBINHO — Thu, 21 Dec 2017 16:58:56 GMT

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?

Re: Combining two subsequen observations

Astounding — Thu, 21 Dec 2017 17:05:45 GMT

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.

Re: Combining two subsequen observations

mkeintz — Thu, 21 Dec 2017 21:59:44 GMT

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;

Re: Combining two subsequen observations

PeterClemmensen — Fri, 22 Dec 2017 11:26:01 GMT

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;