DATA Step, Macro, Functions and more

How to summarise the data and modify the variables

Reply
Regular Contributor
Posts: 189

How to summarise the data and modify the variables

[ Edited ]

 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 .      

 

Please note that I had posted the same question partially as another thread which is closed right now   .

Adding the previous post link here

 

 

https://communities.sas.com/t5/Base-SAS-Programming/How-to-summarise-the-data-to-modify-some-variabl...
         
Appreciate any help with this.         

 

 

 

 

Regards,

Sheeba

Super User
Posts: 13,889

Re: How to summarise the data and


@Sheeba wrote:

 

Please note that I had posted the same question partially as another thread which is closed right now   
 


If the previous thread had a partial answer it might help to provide that here, or at a minimum a link to the other thread. Then provide what needs to added to the previous answer.

 

That might reduce having to answer the same questions that may have been asked in the previous thread.

Regular Contributor
Posts: 189

Re: How to summarise the data and

Hi Ballardw,

 

Thanks for the suggestions.

 

I have edited the post and included the link to previous post.

 

Regards,

Sheeba

Regular Contributor
Posts: 189

Re: How to summarise the data and modify the variables

Appreciate any help on this.

 

Regards,

Sheeba

Ask a Question
Discussion stats
  • 3 replies
  • 70 views
  • 1 like
  • 2 in conversation