Hi All,
I need some guidelines to proceed with my requirement. Here are the details:-
#1) I have a input txt file data as below:-
Row1 -->123456 Transact1 Transact2 Transact3
Row2--> 456789 Transac1 Transact2
Note : We don't know about the position of the data in the file. Numbers in the first position are the employee ids and followed by transaction types.
I have to read the input file and create a dataset "InptData" with two columns and it's corresponding vaues. How we can read this ?
EmployeeID TransactionType
#2) I have queried from database and created another dataset 'Details' with below values:-
TransactionType Service
Transact1 A1
Transact2 A2
Transact3 A1
I have to merge #1 and #2 datasets to determine the Service for each transactions for the employee
.Once we determined the service ,I have to output the data to different fles:-
If service is A1 then output to File1
If service is A2 then output to File2
Could you please someone guide me on the SAS functionality for this requirement ?
Thanks in advance for your help .
Neither of these need hash or array. A data step should work.
@Some hints, look up the trailing @@ or _infile_ for number 1.
For your second question look up the OUTPUT statement.
@AshokD wrote:
Hi All,
I need some guidelines to proceed with my requirement. Here are the details:-
#1) I have a input txt file data as below:-
Row1 -->123456 Transact1 Transact2 Transact3
Row2--> 456789 Transac1 Transact2
Note : We don't know about the position of the data in the file. Numbers in the first position are the employee ids and followed by transaction types.
I have to read the input file and create a dataset "InptData" with two columns and it's corresponding vaues. How we can read this ?
EmployeeID TransactionType
#2) I have queried from database and created another dataset 'Details' with below values:-
TransactionType Service
Transact1 A1
Transact2 A2
Transact3 A1
I have to merge #1 and #2 datasets to determine the Service for each transactions for the employee
.Once we determined the service ,I have to output the data to different fles:-
If service is A1 then output to File1
If service is A2 then output to File2
Could you please someone guide me on the SAS functionality for this requirement ?
Thanks in advance for your help .
Thanks @ChrisNZ
I hope this will work but need a small clarification.
I'm having nearly 100+ transaction types so can you please suggest logic using array or hash instead of merge step so that it performs faster?
Please correct me if I am wrong.
If performance is an issue I would create formats from the details table and apply them in the very first data step. This way you only have one step.
Thanks for the inputs and suggestion.
I will try both the methods and let's see which one suits.
Thanks for your time and help.
Hi All,
Using proc format method is working fine but the requirement is need in hash table lookup.
Could you please suggest a logic on hash table ?
Thanks in advance for your help.
You don't need a merge at all, you do it in one data step with a format:
data cntlin;
input start :$20. label $;
fmtname = "transacts";
type = 'C';
cards;
Transact1 A1
Transact2 A2
Transact3 A1
;
run;
proc format lib=work cntlin=cntlin;
run;
data
a1
a2
;
infile cards truncover;
length empid $6 transactiontype $10;
input;
empid = scan(_infile_,1);
do i = 2 to countw(_infile_);
transactiontype = scan(_infile_,i);
select (put(transactiontype,$transacts.));
when ('A1') output a1;
when ('A2') output a2;
otherwise;
end;
end;
drop i;
cards;
123456 Transact1 Transact2 Transact3
456789 Transact1 Transact2
;
run;
You might want to remove the otherwise to get an ERROR if you catch an unexpected transaction type.
This works:
data HAVE;
informat TRANSACTIONTYPE $10.;
infile cards missover truncover;
input EMPLOYEEID @ ;
do until (TRANSACTIONTYPE=' ');
input TRANSACTIONTYPE $ @;
if TRANSACTIONTYPE ne ' ' then output;
end;
cards;
123456 Transact1 Transact2 Transact3
456789 Transact1 Transact2
run;
proc sort data=HAVE;
by TRANSACTIONTYPE;
run;
data DETAILS;
informat TRANSACTIONTYPE $10.;
input TRANSACTIONTYPE $ SERVICE $;
cards;
Transact1 A1
Transact2 A2
Transact3 A1
run;
data FILE1
FILE2;
merge HAVE DETAILS;
by TRANSACTIONTYPE;
if SERVICE='A1' then output FILE1;
if SERVICE='A2' then output FILE2;
run;
A few assumptions about the data are made here.
Please understand the steps, as described at @Reeza, to correct and fit to your exact needs.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: