I have an input file and i am trying to group it and create a text file in a particular format. Input data vnumber key1 suffx source amount product cust 102XX 2948S X Axis 100 prod1 cust1 102XX 2948S X Axis 50 prod1 cust1 102XX 2948S X Axis 200 prod2 cust2 245TX 1801 Y Base 250 prod1 cust1 245TX 1801 Y Base 320 prod2 cust1 809TX 2341 Z Code 313 prod1 cust1 810TX 2341 Z Code -826.88 prod1 cust1 I am trying to create the following output from above Record type vnumber key1 suffx source Number of lines amount V 102XX 2948S X Axis 350 L 102XX 2948S X Axis 3 350 V 245TX 1801 Y Base 570 L 245TX 1801 Y Base 2 570 V 809TX 2341 Z Code 313 L 809TX 2341 Z Code 1 313 V 810TX 2341 Z Code -413.44 L 810TX 2341 Z Code 1 -413.44 I am trying to sort the input data by vnumber, key1, suffx, source and extract first observation from each by group as record of the type "V" (header) and the same record as Line and remaining records of the same group as L and would like to get the L record count in a variable. I am not sure how to extract the count, get the sum of amounts for each group. proc sort data=test out=test1; by vnumber key_1 suffx source; run; data flags_v; set test1; by vnumber key_1 suffx source; if first.vnumber and first.key_1 and first.suffx =1 and first.source = 1 then do; rec_type="V"; end; else do; if vnumber = lag(vnumber ) and key_1 = lag(key_1) and suffx = lag(suffx) and source = lag(source) then rec_type2 = "L"; else rec_type = "V"; end; run;
************************************************************************************************************************************
Added the below lines to ask an additional questions.
*************************************************************************************************************************************
I have an input file and i am trying to group it and create a text file in a particular format. Input data vnumber key1 suffx source amount product cust 102XX 2948S X Axis 100 prod1 cust1 102XX 2948S X Axis 50 prod1 cust1 102XX 2948S X Axis 200 prod2 cust2 245TX 1801 Y Base 250 prod1 cust1 245TX 1801 Y Base 320 prod2 cust1 809TX 2341 Z Code 313 prod1 cust1 810TX 2341 Z Code -826.88 prod1 cust1
I am trying to create the following output from above
Record type
vnumber
key1
suffx
source
Number of lines
amount
Number of accounting lines
Accounting amount
VAR1
V
102XX
2948S
X
Axis
350
L
102XX
2948S
X
Axis
3
350
2
A
102XX
2948S
X
Axis
2
150
A
102XX
2948S
X
Axis
2
200
P
Payment
V
245TX
1801
Y
Base
570
L
245TX
1801
Y
Base
2
570
2
250
A
245TX
1801
Y
Base
2
320
A
245TX
1801
Y
Base
2
P
Payment
V
809TX
2341
Z
Code
313
L
809TX
2341
Z
Code
1
313
1
A
809TX
2341
Z
Code
1
313
1
313
P
Payment
V
810TX
2341
Z
Code
-413.44
L
810TX
2341
Z
Code
1
-413.44
1
A
810TX
2341
Z
Code
1
-413.44
1
-413.44
P
Payment
I am trying to sort the input data by vnumber, key1, suffx, source and extract first observation from each by group as record of the type "V" (header) and the same record as Line and remaining records of the same group as L and would like to get the L record count in a variable Once I am able to classify the records as V, L ..i need to check the records within L to see if prod and cust are same as prod and cust for previous records. if they are same , then it can be treated as 1 'A'. If either of it is different, it has to be treated as different A. In the above example the first by group has distinct values for prod and cust so it has two accounting lines and amounts also get added according to the accounting lines. Finally I need to add a record 'P' for each group with an additional variable . Appreciate any help with this.
Appreciate any help on this.
Regards,
Sheeba
... View more