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.
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;
How did you summarize your data?
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).
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.