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
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.
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;
Customize a format for it.
proc format;
value fmt
0-1=[percent8.2]
other=[best8.] ;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.