import columns based on data and rename them

hexx18 — Thu, 30 Jun 2016 12:20:18 GMT

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)

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

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

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);

Re: import columns based on data and rename them

ballardw — Fri, 01 Jul 2016 15:54:33 GMT

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.

topic import columns based on data and rename them in SAS Procedures

import columns based on data and rename them

Re: import columns based on data and rename them