topic Re: appending same column data and output last record in SAS Programming

appending same column data and output last record

SMohanReddy — Thu, 07 Sep 2017 05:55:26 GMT

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

Re: appending same column data and output last record

Astounding — Thu, 07 Sep 2017 07:02:53 GMT

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;

Re: appending same column data and output last record

MINX — Fri, 08 Sep 2017 17:02:58 GMT

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