DATA Step, Macro, Functions and more

appending same column data and output last record

Reply
Occasional Contributor
Posts: 19

appending same column data and output last record

proc sort data=delq_array;
by application app_month perf_month;
run;

data want;
set delq_array ;
by application app_month perf_month;
length string $60;
retain string;
string=compress(catx('',string,delq));
if last.application then do;
output;
call missing(string);
end;
run;

 

the aove program currently appends delq column data and outputs based on last.application. But i need to append '.' if there is missing performance based on perf_month column by application.

 

indata is attached. please help me on the logic

 

current output

Application App_month Perf_month string
1 1-Sep-15 1-Sep-17 000112345211000000000000
2 1-Sep-15 1-Jul-17 0001123452000000000
3 1-Sep-15 1-Jan-16

00

 

 

 

required output

Application App_month Perf_month string
1 1-Sep-15 1-Sep-17 000112345211000000000000
2 1-Sep-15 1-Jul-17 0001123452..0.00000000
3 1-Sep-15 1-Jan-16 .0.0
Super User
Posts: 5,513

Re: appending same column data and output last record

Posted in reply to SMohanReddy

One possibility:

 

data want;
set delq_array ;
by application app_month perf_month;
length string $60;
retain string;

if first.application then do;

   location=1;

   string = repeat('.', 59);

end;

else location + month_diff;
substr(string, location, 1) = put(delq, 1.);
if last.application;
run;

 

This assumes that DELQ is numeric.  If it is actually character with a length of $1, the program becomes simpler:

 

substr(string, location, 1) = delq;

Contributor
Posts: 20

Re: appending same column data and output last record

Posted in reply to Astounding

@Astounding Good approach. I simplifier your code and trim out all extra dots based on Month_Diff to make output as similar as post

 

data want;
	set delq_array ;
	by application app_month perf_month;
	length string $60;
	retain string;
	if first.application then string = repeat('.', 60);
	substr(string,Month_Diff,1) = strip(put(delq,best.));
	if last.application then do;
		string = substr(string,1,Month_Diff);
		output;
	end;
run;

 

Ask a Question
Discussion stats
  • 2 replies
  • 82 views
  • 0 likes
  • 3 in conversation