topic Re: Need help on Formats in ODS and Base Reporting

Need help on Formats

KannanBaskar — Wed, 01 Oct 2014 14:32:42 GMT

Hi Team,

I am in need of your help on the appending two datasets when there is change in formats. Please see the example below.

Dataset1 : Formats for all the months (i.e. Jan to Dec) are Percent10.0.

Emp No	Emp Name	Indicator	Jan	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
353280	Kannan	Salary Rate	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
353281	Jagathis	Salary Rate	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	7.14%
353282	Muthuraj	Salary Rate	13%	8%	0%	0%	0%	0%	0%	0%	13%	11%	13%	10.95%
353283	Gowsalya	Salary Rate	8%	11%	14%	9%	8%	11%	11%	9%	10%	10%	15%	10.51%
353284	Nadeem	Salary Rate	8%	7%	5%	5%	12%	3%		9%	23%	4%	5%	7.13%

Dataset2 : Formats for all the months (i.e. Jan to Dec) are 5.0.

Emp No	Emp Name	Indicator	Jan	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
353280	Kannan	Salary	1000	5666	876555	1589	5222	5514	522	98745	36524	12398	5474	1049209
353281	Jagathis	Salary	10	30	54	14	85	64	62	41	10	2	15	111020.2
353282	Muthuraj	Salary	147	52	15	212	5151	5151	512	5188	151	326	20	212122
353283	Gowsalya	Salary	568	84565	65521	6262	1218548	6232	76262	86232	3151223	212	98	12217445
353284	Nadeem	Salary	8656	515	10202	23323	5151	5651	5785	551	54111	515	5155	65125415

Now I need to concatinate these two datasets where the output should be in the below format.

Emp No	Emp Name	Indicator	Jan	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
353280	Kannan	Salary Rate	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
353280	Kannan	Salary	1000	5666	876555	1589	5222	5514	522	98745	36524	12398	5474	1049209
353281	Jagathis	Salary Rate	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	7.14%
353281	Jagathis	Salary	10	30	54	14	85	64	62	41	10	2	15	111020.2
353282	Muthuraj	Salary Rate	13%	8%	0%	0%	0%	0%	0%	0%	13%	11%	13%	10.95%
353282	Muthuraj	Salary	147	52	15	212	5151	5151	512	5188	151	326	20	212122
353283	Gowsalya	Salary Rate	8%	11%	14%	9%	8%	11%	11%	9%	10%	10%	15%	10.51%
353283	Gowsalya	Salary	568	84565	65521	6262	1218548	6232	76262	86232	3151223	212	98	12217445
353284	Nadeem	Salary Rate	8%	7%	5%	5%	12%	3%		9%	23%	4%	5%	7.13%
353284	Nadeem	Salary	8656	515	10202	23323	5151	5651	5785	551	54111	515	5155	65125415

I have user set ,proc append and SQL to get this format But no luck.

Can you please help me on this issue.

Thanks,

Kannan Baskar

Re: Need help on Formats

ballardw — Wed, 01 Oct 2014 15:35:02 GMT

Since only one format can be assigned to a variable (column) I suspect you aren't going to get what you want in a single data set. Is the final form actually wanted for a report as that probably can be made to happen.

Re: Need help on Formats

Cynthia_sas — Wed, 01 Oct 2014 18:02:09 GMT

Hi:

As BallardW explained, a DATASET can only have 1 format applied to a variable (column). However, in a REPORT on a dataset, it is possible to apply different formats to different cells on the REPORT. This is very possible with PROC REPORT using the COMPUTE block and CALL DEFINE. Notice how the row for Alfred is formatted differently than the row for Alice in the final REPORT. So if you get the data arranged so that the final dataset has an INDICATOR variable as you show, then the CALL DEFINE statement does exactly what you want -- in a REPORT, not in the DATASET.

Cynthia

ods _all_ close;

** make some fake data with 2 rows for each obs;
data fakedata(keep=name indicator date1 date2 date3);
set sashelp.class;
where name in ('Alfred', 'Alice');
date1 = age*133 ;
date2 = height*17;
date3 = weight*15;
Indicator = 'Salary';
output;

** now make a fake percent;
date1 = date1 / (height*weight);
date2 = date2 / (height*weight);
date3 = date3 / (height*weight);
indicator = 'Rate';
output;
run;

options center;
ods html file='c:\temp\calldef.html';;

proc report data=fakedata nowd;
title 'Use CALL DEFINE and PROC REPORT';
column name indicator date1 date2 date3;
define name / order;
define indicator / order order=data;
define date1 / display 'Jan' f=comma8.;
define date2 / display 'Feb' f=comma8.;
define date3 / display 'Mar' f=comma8.;
compute date3;
if indicator = 'Rate' then do;
    call define('date1','format','percent9.2');
    call define('date2','format','percent9.2');
    call define('date3','format','percent9.2');
end;
endcomp;
run;
ods html close;

Re: Need help on Formats

Ksharp — Thu, 02 Oct 2014 12:15:26 GMT

Customize a format for it.

proc format;

value fmt

0-1=[percent8.2]

other=[best8.] ;