Enter zero filled row

[sustagens]

Hi, just struggling how to best cope with summarising data with null variants?

One of my summarised datasets look like this:

I need to be able to insert a dummy row, with a value of 3 for Ccy_Order and value of JPY for Ccy, and all the rest of the "EXPYYYYMM" columns to the right should be null.

Sometimes the row for Ccy_Order 2 will be missing, sometimes for 1 or 4 - I just need all four types to be present.

Any help is appreciated.

[PeterClemmensen]

How did you summarize your data?

[Kurt_Bremser]

I have a suspicion that you start initialky with a long dataset format and transpose later. Please post that data in usable form (data step with datalines).

[sustagens]

The initial data is filtered, for just MNCs.

My data has transactions, and sometimes not all the currencies (USD/PHP/JPY/OTHERS) are transacted on, but the report format should be retained for all four whether there were any for the period or not.

PROC SQL;
   CREATE TABLE want AS 
   SELECT DISTINCT Ccy, 
		  Base_YM,
		  (SUM(EXPOSURE)) FORMAT=COMMA23.2 AS EXPOSURE, 
		  Ccy_Order
      FROM have 
      WHERE Trans="MNC"
      GROUP BY Ccy_Order, Ccy, Base_YM
      ORDER BY Ccy_Order, Base_YM;
QUIT;

Here is sample data for my have table:

data have;
  input Ccy:$6. Base_YM:$6. EXPOSURE:COMMA23.2 Ccy_Order:32.;
  format EXPOSURE COMMA23.2 ;
datalines;
USD 201504 30000000 1
USD 201505 30000000 1
USD 201506 30000000 1
USD 201507 30000000 1
USD 201508 30000000 1
USD 201509 30000000 1
USD 201510 30000000 1
USD 201511 30000000 1
PHP 201510 65000000000 2
PHP 201511 50000000000 2
PHP 201512 50000000000 2
PHP 201601 55000000000 2
PHP 201602 60000000000 2
PHP 201910 250600000000 2
OTHERS 201910 8500000000 4
;

I then transposed that so that I have the dates as the columns.

PROC TRANSPOSE DATA=want 
	OUT=TRNS_want (drop=_NAME_)
	PREFIX=EXP
	LABEL=Label
;
	BY Ccy_Order Ccy;
	ID Base_YM;
	VAR EXPOSURE;
RUN; QUIT;

[sustagens · Accepted solution]

Figured it out.

data Ccy_Ref;
  input Ccy_Order:8. Ccy:$10.;
datalines;
3 JPY
4 OTHERS
2 PHP
1 USD
;
run;

DATA want;
 MERGE have ccy_ref;
 BY Ccy Ccy_Order;
 RUN;