The SAS Output Delivery System and reporting techniques

Need help on Formats

Reply
Occasional Contributor
Posts: 19

Need help on Formats

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 NoEmp NameIndicatorJanMarAprMayJunJulAugSepOctNovDecTotal
353280KannanSalary Rate0%0%0%0%0%0%0%0%0%0%0%0%
353281JagathisSalary Rate0%0%0%0%0%0%0%0%0%0%0%7.14%
353282MuthurajSalary Rate13%8%0%0%0%0%0%0%13%11%13%10.95%
353283GowsalyaSalary Rate8%11%14%9%8%11%11%9%10%10%15%10.51%
353284NadeemSalary Rate8%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 NoEmp NameIndicatorJanMarAprMayJunJulAugSepOctNovDecTotal
353280KannanSalary1000566687655515895222551452298745365241239854741049209
353281JagathisSalary103054148564624110215111020.2
353282MuthurajSalary147521521251515151512518815132620212122
353283GowsalyaSalary5688456565521626212185486232762628623231512232129812217445
353284NadeemSalary8656515102022332351515651578555154111515515565125415


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

Emp NoEmp NameIndicatorJanMarAprMayJunJulAugSepOctNovDecTotal
353280KannanSalary Rate0%0%0%0%0%0%0%0%0%0%0%0%
353280KannanSalary1000566687655515895222551452298745365241239854741049209
353281JagathisSalary Rate0%0%0%0%0%0%0%0%0%0%0%7.14%
353281JagathisSalary103054148564624110215111020.2
353282MuthurajSalary Rate13%8%0%0%0%0%0%0%13%11%13%10.95%
353282MuthurajSalary147521521251515151512518815132620212122
353283GowsalyaSalary Rate8%11%14%9%8%11%11%9%10%10%15%10.51%
353283GowsalyaSalary5688456565521626212185486232762628623231512232129812217445
353284NadeemSalary Rate8%7%5%5%12%3% 9%23%4%5%7.13%
353284NadeemSalary8656515102022332351515651578555154111515515565125415

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

Super User
Posts: 11,114

Re: Need help on Formats

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.

SAS Super FREQ
Posts: 8,818

Re: Need help on Formats

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;


different_formats_based_on_indicator.png
Super User
Posts: 9,865

Re: Need help on Formats

Customize a format for it.

proc format;

value fmt

      0-1=[percent8.2]

      other=[best8.] ;

Ask a Question
Discussion stats
  • 3 replies
  • 507 views
  • 0 likes
  • 4 in conversation