topic Proc report in SAS Procedures

Proc report

vnreddy — Tue, 07 Jan 2020 14:55:33 GMT

Stage_transfer opening_stage closing_stage opening_balance closing_balance opening_period closing_period
Stage 1 to 1 1 1 37735.11 36092.9 31-Jan-19 31-Dec-19
Stage 1 to 2 1 2 55841.76 53787 31-Jan-19 31-Dec-19
Stage 1 to 3 1 3 17532.1 16256.83 31-Jan-19 31-Dec-19
Stage 2 to 1 2 1 17442.97 17115.08 31-Jan-19 31-Dec-19
Stage 2 to 2 2 2 64759.82 63959.25 31-Jan-19 31-Dec-19
Stage 2 to 3 2 3 35349.27 34116.02 31-Jan-19 31-Dec-19
Stage 3 to 1 3 1 6084.11 2227.07 31-Jan-19 31-Dec-19
Stage 3 to 2 3 2 57440.35 55322.16 31-Jan-19 31-Dec-19
Stage 3 to 3 3 3 16304.87 14736.68 31-Jan-19 31-Dec-19

Balance business rules:
balance at 1 January 2019 stage 1 (in millions) sum up the opening_balance based on opening_stage 1 e.g., sum(37735.11+55841.76+17532.1)
similarly stage 2 (in millions) sum up the opening_balance based on opening_stage 2 e.g., sum(17442.97+64759.82+35349.27)
stage 3 (in millions) sum up the opening_balance based on opening stage 3 e.g., sum(6084.11+57440.35+16304.87)

closing balance rules:
balance at 31 December 2019 stage 1(in millions) sum up the closing_balance based on closing_stage 1 e.g., sum(36092.9+17115.08+2227.07)
stage 2(in millions) sum up the closing_balance based on closing_stage 2 e.g., sum(53787+63959.25+55322.16)
stage 3(in millions) sum up the closing_balance based on closing_stage 3 e.g., sum(16256.83+34116.02+14736.68)

stage transfer business rules:
transfer to stage 1: sum of closing_balance horizontal 1 to 1, 1 to 2, 1 to 3
2 to 1, 2 to 2, 2 to 3
3 to 1, 3 to 1, 3 to 3

sample example is done in screenshot

Expected output:

Re: Proc report

PaigeMiller — Tue, 07 Jan 2020 15:35:59 GMT

Please provide your data as SAS data step code, following these instructions, so we can actually work with it.

Re: Proc report

vnreddy — Tue, 07 Jan 2020 15:45:01 GMT

Hi @PaigeMiller

sorry for that, actually i am looking for some thing like the attached image (previous post) output format.

I have attached the sample data as well.

DATA WORK.NEMO_IMPAIRMENT_TESTING_0000;
28             LENGTH
29                 Stage            $ 12
30                 opening_stage      8
31                 closing_stage      8
32                 opening_balance    8
33                 closing_balance    8
34                 opening_period     8
35                 closing_period     8 ;
36             FORMAT
37                 Stage            $CHAR12.
38                 opening_stage    BEST12.
39                 closing_stage    BEST12.
40                 opening_balance  BEST12.
41                 closing_balance  BEST12.
42                 opening_period   DATE9.
43                 closing_period   DATE9. ;
44             INFORMAT
45                 Stage            $CHAR12.
46                 opening_stage    BEST12.
47                 closing_stage    BEST12.
48                 opening_balance  BEST12.
49                 closing_balance  BEST12.
50                 opening_period   DATE9.
51                 closing_period   DATE9. ;
52             INFILE 'M:\SASWORK\_TD14660_PBS-SAS-CP01_\#LN00138'
53                 LRECL=46
54                 ENCODING="WLATIN1"
55                 TERMSTR=CRLF
56                 DLM='7F'x
57                 MISSOVER
58                 DSD ;
2                                                          The SAS System                             09:21 Tuesday, January 7, 2020

59             INPUT
60                 Stage            : $CHAR12.
61                 opening_stage    : BEST32.
62                 closing_stage    : BEST32.
63                 opening_balance  : BEST32.
64                 closing_balance  : BEST32.
65                 opening_period   : BEST32.
66                 closing_period   : BEST32. ;
67         RUN;

Re: Proc report

vnreddy — Tue, 07 Jan 2020 18:58:49 GMT

Hi,
Can someone please help me with above requirement

Re: Proc report

PaigeMiller — Tue, 07 Jan 2020 19:03:45 GMT

Please provide your data as SAS data step code, following these instructions, so we can actually work with it.