Help using Base SAS procedures

import columns based on data and rename them

Reply
Contributor
Posts: 22

import columns based on data and rename them

[ Edited ]

I have two text files t1 and t2 and t6  and the output should be t3 and t4 and t7 (sas datasets) respectively . While importing i have to import in such a way that when the first coulmn has numberic data (1,2,10) i want that to be imported as numeric column (Memid) else when first column has character data and i want it to be character column (numV)

 

T1

 

item0 item1 item2 item3 item4 item5 item6 item7 item8 
"C,P,0,9" "C,18,P,7" "C,18,P,7" "C,18,P,7" "C,18,P,7"                                       
Memid xy Price Credit price Debit Price yy Price other price all price   nhh price                                    
1 356.00 234.00 23434 5757    7357 736767 63666                                
2 4878 3546.00 6565 5656 7467 6686 7688                                    
10 74676 6478.00 65465 83648   6888    5767    6767.00

 

T2
item0 item1 item2 item3 item4 item5 item6 item7 item8 
"C,P,0,9" "C,18,P,7" "C,18,P,7" "C,18,P,7" "C,18,P,7"                                       
numV xy Price Credit price Debit Price yy Price other price all price                                        
ABA 456.00 236.00 23434 5757    7357 736767 63666                                
CBC 4878 3546.00 6565 5656 7467 6686 7688                                    
DBA 64676 6478.00 65465 83648   6888    5767    6767.00

 

T6
item0 item1 item2 item3 item4 item5 item6 item7 item8 
"C,P,0,9" "C,18,P,7" "C,18,P,7" "C,18,P,7" "C,18,P,7"                                       
group    Pin xy Price Credit price Debit Price yy Price other price all price                                        
group 01 3 456.00 236.00 23434 5757    7357 736767 63666                                
group 01  -99 4878 3546.00 6565 5656 7467 6686 7688                                    
group 02  4 64676 6478.00 65465 83648   6888    5767    6767.00

group 03  10 84676 6478.00 65465 83648   6888    4767    6767.00

 

 

T3

                                       
var1 xy      Credit  Debit    yy     other  all   nhhh                                      
1 356.00 234.00 23434 5757    7357 736767 63666                                
2 4878 3546.00 6565 5656 7467 6686 7688                                    
10 74676 6478.00 65465 83648   6888    5767    6767.00

 

T4
                                       
var1 xy      Credit  Debit    yy     other  all   nhhh                                        
ABA 456.00 236.00 23434 5757    7357 736767 63666                                
CBC 4878 3546.00 6565 5656 7467 6686 7688                                    
DBA 64676 6478.00 65465 83648   6888    5767    6767.00

 

T7

 

group    Pin          xy    Credit     Debit  yy  other  all                                        
group 01 -99      456.00 236.00 23434   5757    7357 736767 63666                                
group 01  3        4878   3546.00  6565    5656    7467 6686 7688                                    
group 02  4         64676  6478.00  65465 83648    6888    5767    6767.00

group 03  10       84676  6478.00   65465 83648   6888    4767    6767.00

 

Currently i have written below code but i need to modify code

to get output t3 and t4 and t7 . Can anyone suggest me ?

 

%macro tm(data,filenm);

DATA WORK.XX;
length VAR1 $15 var2 $4;
 INFILE "C\Desktop"  LRECL=1000  DLM='09'x pad dsd firstobs=3;
INPUT var1 $ var2 $ var3 $ ;
run;
%mend;
%tm(txt,Pricing_03_20_2015);

 

Super User
Posts: 11,343

Re: import columns based on data and rename them

Are T3 and T4 ever going to be combined for analysis? If so then you really do not want to change the variable types for var1 as it seems you are thinking of with your Memid and NumV variables. Typically any sort of identification variable is not subject to arithmetic (Question: what does the result actuall mean when you add credit card account numbers for example?) So you could likely simplify your process by providing a single identification variable. You might want to have a variable that indicates what type of origination file that is important.

 

Since your T6 file actually has a different structure - the PIN variable- you need to provide some way to indicate that when reading that file format. Easiest might be file format variable and test that value to use the correct input statement.

 

I am somewhat confused about reading what your example data shows as likely money values (price, credit price, debit price) that you seem to be reading as character (var2 $ var3$) and not actually using what appear to be your desired variable names xy, Credit,  Debit    yy, other, all and nhhh.

Ask a Question
Discussion stats
  • 1 reply
  • 232 views
  • 0 likes
  • 2 in conversation